Using VLOOKUP() But "Like" Values

1. Using VLOOKUP() But "Like" Values

Hi!

I just want to ask how can I use VLOOKUP() to look a match range to another range but instead of it looking for the EXACT values, I want to look for "LIKE" values. Example:

Column A
FIRE
ICE
WIND
WATER

Column B
FIRE WORKS
ICE BERG
WIND VANE
WATER BASE

As you can see, if I use VLOOKUP() in here it wouldn't find any match. What I want is to do is look up Column A to Column B and find matches if their values are similar to each other.

Thank you.  Register To Reply

2. not exactly what you want to do, but this array formula will give you the position of the "similar" item in column B. This assumes column a rows 1-4, and column B rows 1-4 are the ranges, and by "similar" you mean the column A item is the first word in the string IN COLUMN B.

You could then use this to find and adjacent text or number via offset or index function

=MATCH(a1,LEFT(\$b\$1:\$b\$4,LEN(a1)),0)  Register To Reply

3. Hi. I tried your suggestion, it doesn't work. I do understand the formula but it doesn't work even if I tweak or adjust it.  Register To Reply

4. Originally Posted by charmedcharmer
Hi. I tried your suggestion, it doesn't work. I do understand the formula but it doesn't work even if I tweak or adjust it.
it wiorks as

=OFFSET(B\$1,MATCH(A1,LEFT(\$B\$1:\$B\$8,LEN(A1)),0)-1,0)

ctrl-shift-enter for Array formula.

---  Register To Reply

5. OK. Thanks. Ill try this one... Thank you very much.  Register To Reply