CODEWITHSUNDEEP
excelexcel-formula
Aurelius
Aurelius

Reputation: 485

Combining or removing volatile functions/Outputting a column letter greater than Z

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?

Upvotes: 0

Views: 121

Answers (1)

tigeravatar
tigeravatar

Reputation: 26660

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.

Upvotes: 1

Related Questions