LOOKUP function with SEARCH and INDIRECT

Hello,

I'm stuck on a part of my spreadsheet

I have a worksheet that is pulling formulas based on text that is pasted in. I found a formula online that has been very helpful (but confusing), allowing me to look up a column of text items within one cell and return a result based on the item found.

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Lists!$A$2:$A$1000&" "," "&H2&" "),Lists!$B$2:$B$1000)

This is searching for any text listed in a cell between A2:A1000 inside of cell H2 and returning the corresponding result from B2:B1000. I do not know why the " " are necessary at the beginning and end of the SEARCH function, but the whole formula doesn't work if they aren't there.

I need this formula to reference the H column and the row the formula is on, so I thought I could use an indirect function....

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Lists!$A$2:$A$1000&" "," "&INDIRECT("H"&ROW())&" "),Lists!$B$2:$B$1000)

However, this returns #N/A

Anybody know why INDIRECT would break it?



Thank you!

Try removing the last ) in this section INDIRECT("H"&ROW())&" ",

Try:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Lists!$A$2:$A$10&" "," "&INDIRECT(INDIRECT(TRANSPOSE(ADDRESS(ROW(),8))))&" "),Lists!$B$2:$B$10)

The ADDRESS(ROW(),8) translates to "H"&ROW()

The TRANSPOSE converts the array range returned by the SEARCH() function to a single value...

The nested INDIRECT is needed because the inner INDIRECT returns the value (cell reference) that is in H&ROW(), then the outer one returns what is in that indirect reference.

Try it like this:

=LOOKUP(32768,SEARCH(" "&Lists!$A$2:$A$1000&" "," "&T(INDIRECT("H"&ROW()))&" "),Lists!$B$2:$B$1000)

The concatenated spaces make sure that you don't get "false positives".

For example, if you search for "try" it won't match with "trying".

" "try" " will not be found in " "trying" "

I guess I had assumed you have a cell reference in column H that you needed to redirect too....

but glad it worked

Originally Posted by bubbacheese

This works too, awesome.

Good deal!

One question, why the "T" before the INDIRECT ?

No one really knows for sure WHY INDIRECT("H"&ROW()) throws an error. This happens when an array is evaluated in the INDIRECT function. The ROW() function returns an array, even if it's a single element.

I've seen some explanations that say the reference gets "dereferenced".

Right!

However, I do know that including the T(...) function when the data is TEXT fixes the problem.