VLookup for numbers contained within cells with numbers and text

[cdubs34]

Hello,

I am trying to vlookup (or whatever the necessary function may be) so that in the example below, I can match the numbers in the Code column to their match in the Brand column and have the corresponding cell contents of the Category column get put into the Output column.

Hopefully this makes sense. Your help is much appreciated. Thank you.

<colgroup><col style="mso-width-source:userset;mso-width-alt:7862;width:161pt" width="215"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> </colgroup><tbody>
[TD="class: xl22"][/TD]

[TD="class: xl22"][/TD]

</tbody>

[TABLE="width: 500"]
<tbody>[TR]
[TD]Brand
[/TD]
[TD]Category
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Code
[/TD]
[TD]Output
[/TD]
[/TR]
[TR]
[TD]123 Brand A
[/TD]
[TD]Clothes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]456
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456 Brand B
[/TD]
[TD]Food
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]123
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]789 Brand C
[/TD]
[TD]Stuff
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]789
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[circledchicken]

Hi,

Perhaps use a wildcard like this:

Excel Workbook
	A	B	C	D	E
1	Brand	Category		Code	Output
2	123 Brand A	Clothes		456	Food
3	456 Brand B	Food		123	Clothes
4	789 Brand C	Stuff		789	Stuff
Sheet1

Excel 2010

Cell Formulas
Range	Formula
E2	=VLOOKUP(D2&"*",A$2:B$4,2,0)

 

[cdubs34]

Thank you very much. That worked perfectly. Should have asked this in the original post, but can you also let me know how i would do this exercise the opposite way. So vlookup on the numbers in column A to match up with the numbers in column D. Thanks again for your help

 

[circledchicken]

Thank you very much. That worked perfectly. Should have asked this in the original post, but can you also let me know how i would do this exercise the opposite way. So vlookup on the numbers in column A to match up with the numbers in column D. Thanks again for your help

Your welcome. Perhaps try these alternatives (the first if you know your numbers are always 3 digits:

Excel Workbook
	A	B	C	D	E	F
1	Brand	Category (Method 1)	Category (Method 2)		Code	Output
2	123 Brand A	Clothes	Clothes		456	Food
3	456 Brand B	Food	Food		123	Clothes
4	789 Brand C	Stuff	Stuff		789	Stuff
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=VLOOKUP(--LEFT(A2,3),E$2:F$4,2,0)
C2	=VLOOKUP(--LEFT(A2,FIND(" ",A2)),E$2:F$4,2,0)