partial match vlookup with string

DChicoine

New Member
Joined
Jul 29, 2016
Messages
4
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Jul 29, 2016
Messages
4
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Forum statistics

Threads
1,141,294
Messages
5,705,537
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top