Locate approximate match

essentially, all you need to change is the reference of the first index array. this will tell it to only pick the cell value from column A, rather than column B.

Please Login or Register  to view this content.

I think that formula is the one you're looking for?
to be honest, i'm not sure what you're looking for. you need to explain exactly what you are trying to do.
for example:
I want cell D2 to be the formula, to search through column A, to find the cell value with text from cell B2.

i'm assuming that's what you want, so the formula above seems to work for that. let me know though.

Hi,

Your formula should be:

=INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(B2,A:A)),,),0))

Regards

@peterrc

"Select a Cell D2 (formula), click fx to the left of the formula (in the formula bar), select either argument and row 7 is selected (wrong)."

Actually, the result of 7 for the evaluation of this part of the formula: MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A:A,B2)),,),0) that the OP currently has in cell D2 is perfectly correct. It's just that it's the wrong formula for what they want to do!

It is, in effect, asking for the row number of the first entry in column A which can be found within the string in cell B2. None of the entries in rows 1-6 meet that criteria; however, the entry in the 7th row (a blank) is to be found within the string in B2, and hence the result of 7.

Regards

@peterrc

Sure - no worries. Actually, as an interesting aside to this, (and to redeem myself - I was not strictly correct in stating that the first match was found as a result of a blank being found within the string), it appears that the SEARCH function will return 1 when the search string is "", no matter what the contents of the searched cell, i.e.:

=SEARCH("",A1) will return 1, regardless of the content of A1 (provided it's not an error, of course).

Regards

You're welcome.