Vlookup with multiple entries in each cell

[nora1212]

I have a spreadsheet with a vlookup table which returns the cost based on a column of part numbers. However, some of the part number cells have multiple part numbers within the same cell separated by a return. Is there a formula or mod to the vlookup formula to allow this to work on both the cells with one part number entry, and with multiple part number entries in one cell? E.g. Could "20" be returned if part no is "NN5555"

Part no	Cost
NN1234	10
NN1111 NN5555 NN8978	20
NN6666	30

 

[Toadstool]

Hi Nora1212,

If you can be sure the part number is not contained within another part number then a wildcard VLOOKUP would work.

Nora1212.xlsx
	A	B	C	D	E	F
1	Part no	Cost			Find	Cost
2	NN1234	10			NN1234	10
3	NN1111 NN5555 NN8978	20			NN1111	20
4	NN6666	30			NN5555	20
5					NN8978	20
6					NN6666	30
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=VLOOKUP("*"&E2&"*",$A$2:$B$7,2,0)

 

[nora1212]

Hi. Thanks for your help but there might be a chance that the part number is partially contained in another e.g. there might be NN5555 and NN55, in which case the vlookup stops at N55 unless sorted in order which is not possible with the multiple entries in one cell. I haven't got the data in front of me right now so i will check when i'm back in work. There is a chance that there are no instances like this. I'll update when i know. Thanks again.

 

[Toadstool]

In that case I'd need a worker column to split out each cell with a carriage return:

Nora1212.xlsx
	A	B	C	D	E	F	G
1	Part no	Worker	Cost			Find	Cost
2	NN1234	{NN1234{	10			NN1234	10
3	NN1111 NN5555 NN8978	{NN1111{NN5555{NN8978{	20			NN1111	20
4	NN6666	{NN6666{	30			NN5555	20
5	NN1111 NN55xx NN8978	{NN1111{NN55xx{NN8978{	40			NN8978	20
6	NN11 NNN55	{NN11{NNN55{{	50			NN6666	30
7	NN1111 NN5593939 NN8978	{{NN1111{NN5593939{NN8978{	60			NN55	70
8	NN1111 NN55 NN88 NN8978	{NN1111{NN55{NN88{NN8978{	70			NN11	50
9						NN88	70
10						NN5593939	60
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B8	B2	="{"&SUBSTITUTE(A2,CHAR(10),"{")&"{"
G2:G10	G2	=VLOOKUP("*{"&F2&"{*",$B$2:$C$9999,2,0)

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?
In your version of Excel you may need to confirm the formula with Ctrl+Shift+Enter, not just Enter

21 01 30.xlsm
	A	B	C	D	E
1	Part no	Cost		Find	Cost
2	NN1234	10		NN1234	10
3	NN1111 NN5555 NN8978	20		NN1111	20
4	NN6666	30		NN6666	30
5	NN55	40		NN55	40
6				NN5555	20
7				NN8978	20
Lookup Cost

Cell Formulas
Range	Formula
E2:E7	E2	=LOOKUP(9.99E+307,SEARCH(CHAR(10)&D2&CHAR(10),CHAR(10)&A$2:A$8&CHAR(10)),B$2:B$8)

 

[nora1212]

In that case I'd need a worker column to split out each cell with a carriage return:

Nora1212.xlsx
	A	B	C	D	E	F	G
1	Part no	Worker	Cost			Find	Cost
2	NN1234	{NN1234{	10			NN1234	10
3	NN1111 NN5555 NN8978	{NN1111{NN5555{NN8978{	20			NN1111	20
4	NN6666	{NN6666{	30			NN5555	20
5	NN1111 NN55xx NN8978	{NN1111{NN55xx{NN8978{	40			NN8978	20
6	NN11 NNN55	{NN11{NNN55{{	50			NN6666	30
7	NN1111 NN5593939 NN8978	{{NN1111{NN5593939{NN8978{	60			NN55	70
8	NN1111 NN55 NN88 NN8978	{NN1111{NN55{NN88{NN8978{	70			NN11	50
9						NN88	70
10						NN5593939	60
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B8	B2	="{"&SUBSTITUTE(A2,CHAR(10),"{")&"{"
G2:G10	G2	=VLOOKUP("*{"&F2&"{*",$B$2:$C$9999,2,0)

Toadstool - that worked perfectly. Thanks very much! Much appreciated.

 

[nora1212]

Welcome to the MrExcel board!

Does this do what you want?
In your version of Excel you may need to confirm the formula with Ctrl+Shift+Enter, not just Enter

21 01 30.xlsm
	A	B	C	D	E
1	Part no	Cost		Find	Cost
2	NN1234	10		NN1234	10
3	NN1111 NN5555 NN8978	20		NN1111	20
4	NN6666	30		NN6666	30
5	NN55	40		NN55	40
6				NN5555	20
7				NN8978	20
Lookup Cost

Cell Formulas
Range	Formula
E2:E7	E2	=LOOKUP(9.99E+307,SEARCH(CHAR(10)&D2&CHAR(10),CHAR(10)&A$2:A$8&CHAR(10)),B$2:B$8)

Thanks Peter. I'm sure your solution works too, but i tried toadstool's solution first and that worked so i'll stick with that. Thanks for your effort though. Appreciated.

 

[Peter_SSs]

Thanks Peter. I'm sure your solution works too, but i tried toadstool's solution first and that worked so i'll stick with that. Thanks for your effort though. Appreciated.

No problem, glad you got something that worked for you. The difference is that my solution, if it works for you, doesn't require the extra helper/worker column.

 

[Toadstool]

Toadstool - that worked perfectly. Thanks very much! Much appreciated.

You're welcome!
Thanks for the feedback.