Locate approximate match

[escapes88]

Hi,

Please refer to the attached spreadsheet.

Column A string of text makes up part of Column B.
Using the formula (refer to spreadsheet), I am able to locate the text in Column A from Column B. But I need it the other way round.

Could someone help me with the tweak (or change) the formula? I can't seem to be able to.

escapeT.xlsx

[Apelcius]

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.

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

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.

[peterrc]

Hi escapes88,

Please see attached.
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).
Repeat for Cell E2 and row 2 is selected (correct).
There is no difference between the formulas (except col and row references) so not sure what's causing this incorrect row selection.
However, if you switch cols A and B, formulas in cols D and E both work (???).
Would a simple Yes on No in cols D and E suffice ?

Regards

peterrc

[XOR LX]

@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]

Hi XOR LX,

Thanks for the explanation - very much appreciated - at least I now know why it wasn't working.

Regards

peterrc

[escapes88]

Hi,

Thanks for the reply.

And yes, I want cell D2 to be the formula, through column A, to find the cell value with text from cell B2 and the results should be from coloum A.

But the formula doesnt seem to work when I drag it down.

[XOR LX]

Hi,

Your formula should be:

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

Regards

[XOR LX]

@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

[escapes88]

Thank you so much, XOR LX your formula works.

[XOR LX]

You're welcome.