0

Following on from advice given on this question: Return the column letter rather than number with column "AA" onwards

I have the following formula: =CHAR(SMALL(IF(ISNUMBER(SEARCH($AK$11, INDIRECT($AA$998&"A1:AE9999"))),1*COLUMN(INDIRECT($AA$998&"A‌​1:AE9999"))),ROWS(A$‌​1:A1))+96)

In essence it will search the sheet given in AA998 and the range A1:AE9999 for the word/phrase specified in AK11 and return the column number, then the column number is converted to its column letter.

This is necessary as I need to use it as part of an INDIRECT in the formula below:

=IFERROR(IF(OR(ISNUMBER(VALUE(RIGHT(INDIRECT($AA$2 & $AG$4 & B4),2)))=FALSE,LEN(SUBSTITUTE(INDIRECT($AA$2& $AG$4 & B4)," ",""))>3),"0",RIGHT(INDIRECT($AA$2& $AG$4 & B4),2)),0)

This formula will check to see IF the right 2 characters of the destination cell are not numbers OR the contents of the cell are greater than 3 characters. If either are true just output 0. If false output the right 2 most characters.

My first formula is in cell AG4. AA2 is a sheet name. B4 is a row number

Example of use:

The sheet name that the INDIRECT refers to, contains columns of data. Lets say 5 columns with the first row containing the titles; Blue, Red, Yellow, Green, Orange. Underneath the titles are data. The data is inconsistent and could mostly be junk so I want to extract only specific data in specific cells (Criteria in my 2nd formula) Also, the columns could be in a different order, e.g Red, Orange, Yellow, Blue, Green.

Now, lets say AK11 contains Yellow. My first formula will look for the column containing Yellow as the title and return the letter, my second formula now takes this letter (Along with sheet name and row number) and perform its checks. If the checks are ok, then output the right 2 numbers of the target cell.

Problem 1: The first formula will not return a column letter greater than Z, so if the results of the search is in column AA this will not be the output. Ruining all subsequent formula. How can I obtain the actual column letter?

Problem 2: How can I combine or improve these formula to remove some of the volatile functions or make the formula more efficient overall?

Improve this question

1 Answer 1

Reset to default
1

To do it all in a single formula:

=IF(OR(NOT(ISNUMBER(--RIGHT(INDEX(INDIRECT($AA$998&"A1:E999"),B4,MATCH("*"&AK11&"*",INDIRECT($AA$998&"A1:E1"),0)),2))),LEN(SUBSTITUTE(INDEX(INDIRECT($AA$998&"A1:E999"),B4,MATCH("*"&AK11&"*",INDIRECT($AA$998&"A1:E1"),0))," ",""))>3),0,--RIGHT(INDEX(INDIRECT($AA$998&"A1:E999"),B4,MATCH("*"&AK11&"*",INDIRECT($AA$998&"A1:E1"),0)),2))

However, I would recommend splitting it into two formulas anyway, so that we don't have to keep calling the Index(Indrect(... over and over. So in your first formula cell (mentioned as AG4):

=INDEX(INDIRECT($AA$998&"A1:E999"),B4,MATCH("*"&AK11&"*",INDIRECT($AA$998&"A1:E1"),0))

And then in the second formula cell:

=IF(OR(NOT(ISNUMBER(--RIGHT(AG4,2))),LEN(SUBSTITUTE(AG4," ",""))>3),0,--RIGHT(AG4,2))

Note that these formulas assume the headers are in row 1 of the data sheet as stated in your example.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

What would need to be done if the headers were not in row 1? As in my other question finding and returning the row was not an issue, so I didn't post the code but it is =SMALL(IF(ISNUMBER(SEARCH($AK$5, INDIRECT($AA$2&"A1:AE9999"))),1*ROW(INDIRECT($AA$2&"A1:AE9999"))+1),ROWS(A$1:A1)) However, I will now need to a find the row as well as the column if I am to do it this way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.