Closest Match Formula

[bflan0524]

hello i am trying to formula to identify if a value is in both columns, the entries are in text format for example below
so in the below i would have a column C where i did a lookup of the value in column B compared to column A and it would return the value in column A, does that make sense? right now if i did a normal lookup it would come back #NA because column A does not match column B exactly

Column A	Column B
John Doe Inc	John Doe

 

[Kamolga]

If you look up “*”&B1&”*” it should work (* can be used to mean whatever before or after)

 

[bflan0524]

If you look up “*”&B1&”*” it should work (* can be used to mean whatever before or after)

thanks for replying but i am confused so what would be the exact formula if i was entering in say column C

 

[Kamolga]

Hi,

I would use

=MATCH("*"&B1&"*";A:A;0)

. It will get you a number (line number) if it exists, an error (#NA) if it does not.

 

[Legacy 456129]

Since the error of a non-match when searching 'exact' is N/A, I'd use IFNA(Match(n, range, 0), "Not found")

Office 365 has a new improved version of Match, XMatch which allows with a 4th parm to specify to look up or down, assuming sorted data, which if it is, without the exact match criterion is orders of magnitude faster on large data sets. Description you find at SumProduct Wrote to MS how disappointing it was there was no N/A handling which is included in the new XVlookup. No more IFNA IFERROR.

 

[taurean]

You can use following constructs as well (they have been posted on MrExcel many a time)

Case sensitive
=LOOKUP(2^15,FIND(B1,A:A,1),A:A)

If it should not be case sensitive then
=LOOKUP(2^15,SEARCH(B1,A:A,1),A:A)

 

[bflan0524]

You can use following constructs as well (they have been posted on MrExcel many a time)

Case sensitive
=LOOKUP(2^15,FIND(B1,A:A,1),A:A)

If it should not be case sensitive then
=LOOKUP(2^15,SEARCH(B1,A:A,1),A:A)

thank you the search function worked pretty well on most of it, but i have a question, one example where it didnt return a value when i would have hoped it to

Column A	Column B
BAIM ENTERPRISES INC	Baim Enterprises Inc/McDonalds

 

[taurean]

thank you the search function worked pretty well on most of it, but i have a question, one example where it didnt return a value when i would have hoped it to

Column A	Column B
BAIM ENTERPRISES INC	Baim Enterprises Inc/McDonalds

That's because the function tries to search whole string in column B with column A. In this particular case you need to reverse the logic i.e.
=LOOKUP(2^15,FIND(A:A,B1,1),A:A)

 

[bflan0524]

That's because the function tries to search whole string in column B with column A. In this particular case you need to reverse the logic i.e.
=LOOKUP(2^15,FIND(A:A,B1,1),A:A)

it returns a 0 when i do this now, as opposed to the name, does that make sense?

 

[taurean]

it returns a 0 when i do this now, as opposed to the name, does that make sense?

Arrgh, I didn't see that the case doesn't match and there will be blank cells. Please test with below tweak.
=LOOKUP(2^15,SEARCH(A:A,B3,1)/LEN(A:A),A:A)
Note: These formulas will be quite taxing on the worksheet. See if you can limit to specific ranges on your sheet!