partial match vlookup with string

[DChicoine]

I'm trying to do a vlookup with a partial match. I have a set of Item IDs and I'm trying to identify their item categories. Each item of the same category has the same abbreviation as part of their ID.

IDs
ABC12342 xyz
DEF73829 yzx
GHI93592 zyx

Key
ABC = Category 1
DEF = Category 2
GHI = Category 3

I'm trying to figure out a formula to put the corresponding category next to the item codes.

 

[AliGW]

Try this:

=VLOOKUP(LEFT(A2,3),$A$7:$B$9,2)

$A$7:$B$9 is your lookup table with key codes in column A and category names in column B.

 

[Aladin Akyurek]

The lay-out is not clear. Does the upper exhibit consist of 1 column or 2 columns? The same holds for the lower exhibit...

 

[DChicoine]

Upper is one is one column, lower is two. they are on separate pages.

 

[Aladin Akyurek]

Upper is one is one column, lower is two. they are on separate pages.

In B2 of Sheet1 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH("|"&Sheet2!$A$2:$A$4,"|"&$A2),Sheet2!$B$2:$B$4)

 

[DChicoine]

=VLOOKUP(LEFT(A2,3),$A$7:$B$9,2,False) works, but is there a way to make it so it gives back the category if the ID contains the abbreviation anywhere in it?

i.e.
ABC12342 xyz -> Category 1
xyz ABC10391 -> Category 1

 

[Aladin Akyurek]

=VLOOKUP(LEFT(A2,3),$A$7:$B$9,2,False) works, but is there a way to make it so it gives back the category if the ID contains the abbreviation anywhere in it?

i.e.
ABC12342 xyz -> Category 1
xyz ABC10391 -> Category 1

See post #5...

 

[DChicoine]

In B2 of Sheet1 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH("|"&Sheet2!$A$2:$A$4,"|"&$A2),Sheet2!$B$2:$B$4)

-Cheers

 

[Aladin Akyurek]

-Cheers

You are welcome.