# Vlookup with multiple entries in each cell

#### nora1212

##### New Member
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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

##### Well-known Member
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
ABCDEF
1Part noCostFindCost
2NN123410NN123410
3NN1111 NN5555 NN897820NN111120
4NN666630NN555520
5NN897820
6NN666630
Sheet1
Cell Formulas
RangeFormula
F2:F6F2=VLOOKUP("*"&E2&"*",\$A\$2:\$B\$7,2,0)

#### nora1212

##### New Member
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.

##### Well-known Member
In that case I'd need a worker column to split out each cell with a carriage return:

Nora1212.xlsx
ABCDEFG
1Part noWorkerCostFindCost
2NN1234{NN1234{10NN123410
3NN1111 NN5555 NN8978{NN1111{NN5555{NN8978{20NN111120
4NN6666{NN6666{30NN555520
5NN1111 NN55xx NN8978{NN1111{NN55xx{NN8978{40NN897820
6NN11 NNN55 {NN11{NNN55{{50NN666630
7 NN1111 NN5593939 NN8978{{NN1111{NN5593939{NN8978{60NN5570
8NN1111 NN55 NN88 NN8978{NN1111{NN55{NN88{NN8978{70NN1150
9NN8870
10NN559393960
Sheet1 (2)
Cell Formulas
RangeFormula
B2:B8B2="{"&SUBSTITUTE(A2,CHAR(10),"{")&"{"
G2:G10G2=VLOOKUP("*{"&F2&"{*",\$B\$2:\$C\$9999,2,0)

#### Peter_SSs

##### MrExcel MVP, Moderator

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
ABCDE
1Part noCostFindCost
2NN123410NN123410
3NN1111 NN5555 NN897820NN111120
4NN666630NN666630
5NN5540NN5540
6NN555520
7NN897820
Lookup Cost
Cell Formulas
RangeFormula
E2:E7E2=LOOKUP(9.99E+307,SEARCH(CHAR(10)&D2&CHAR(10),CHAR(10)&A\$2:A\$8&CHAR(10)),B\$2:B\$8)

#### nora1212

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

Nora1212.xlsx
ABCDEFG
1Part noWorkerCostFindCost
2NN1234{NN1234{10NN123410
3NN1111 NN5555 NN8978{NN1111{NN5555{NN8978{20NN111120
4NN6666{NN6666{30NN555520
5NN1111 NN55xx NN8978{NN1111{NN55xx{NN8978{40NN897820
6NN11 NNN55 {NN11{NNN55{{50NN666630
7 NN1111 NN5593939 NN8978{{NN1111{NN5593939{NN8978{60NN5570
8NN1111 NN55 NN88 NN8978{NN1111{NN55{NN88{NN8978{70NN1150
9NN8870
10NN559393960
Sheet1 (2)
Cell Formulas
RangeFormula
B2:B8B2="{"&SUBSTITUTE(A2,CHAR(10),"{")&"{"
G2:G10G2=VLOOKUP("*{"&F2&"{*",\$B\$2:\$C\$9999,2,0)
Toadstool - that worked perfectly. Thanks very much! Much appreciated.

#### nora1212

##### New Member

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
ABCDE
1Part noCostFindCost
2NN123410NN123410
3NN1111 NN5555 NN897820NN111120
4NN666630NN666630
5NN5540NN5540
6NN555520
7NN897820
Lookup Cost
Cell Formulas
RangeFormula
E2:E7E2=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

##### MrExcel MVP, Moderator
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.

##### Well-known Member
Toadstool - that worked perfectly. Thanks very much! Much appreciated.

You're welcome!
Thanks for the feedback.

Replies
4
Views
117
Replies
2
Views
166
Replies
2
Views
78
Replies
1
Views
260
Replies
6
Views
93

1,127,491
Messages
5,625,075
Members
416,068
Latest member
seba_s

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

### Which adblocker are you using?

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

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