# 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.

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)

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.

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.

---

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1