Using VLookup when text isn't an exact match

K

Ken K

I am trying to match a list of data using Vlookup with an array that isn't an
exact match. Example, cell may have "red, white & blue" and the array has
"red". I want this to return a positive match, not N/A#.

Is there a way to do this using Vlookup or some other function?
 
L

Luke M

Need to use an array** function using SEARCH (or FIND, if you want
case-sensitive).

Let's say your lookup column is in A2:A10, return column is C2:C10, and
value to find is in A1

=INDEX(C2:C10,MATCH(TRUE,ISNUMBER(SEARCH(A1,A2:A10)),0))

**Array formulas need to be confirmed using Ctrl+Shift+Enter,, not just Enter.
 
D

Domenic

Assuming that F2:F10 is the lookup column, G2:G10 is the return column,
and A2 contains the lookup value, try...

=LOOKUP(9.99999999999999E+307,SEARCH($F$2:$F$10,A2),$G$2:$G$10)

Note, however, if F2:F10 contains or can contain empty/blank cells, try
the following formula instead...

=LOOKUP(9.99999999999999E+307,IF($F$2:$F$10<>"",SEARCH($F$2:$F$10,A2)),$G
$2:$G$10)

....confirmed with CONTROL+SHIFT+ENTER. Also, note that if for example
the lookup value is 'reddish, white & blue', the formula will return a
match, since 'red' occurs within the text string.
 
N

naushadparpia

This was super helpful. Thanks so much!

Quick question to expand on this. What if there are multiple return values. For example, I will trying to search for "Bob" and here are multiple Bobs and I want to return all of their last names. Is there a way to do more than one?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP & Data Validation 0
Vlookup question 3
Vlookup and return sheet name also 2
Index Match, IF or Vlookup 0
Formula Using Vlookup & Match 3
Vlookup & exact match 4
Vlookup 1
Match and Vlookup issue 2

Top