Using VLookup when text isn't an exact match
 

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?
--
Ken K

Using VLookup when text isn't an exact match
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ken K" wrote:

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?
--
Ken K

Using VLookup when text isn't an exact match
 

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<"",SE ARCH($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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Ken K wrote:

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?

Using VLookup when text isn't an exact match
 

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?

On Wednesday, 19 August 2009 10:33:01 UTC-4, Luke M wrote:
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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ken K" wrote:

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?
--
Ken K