Vlookup with multiple entries in each cell

nora1212

New Member
Joined
Jan 29, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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 noCost
NN123410
NN1111
NN5555
NN8978
20
NN666630
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,378
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jan 29, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,378
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 29, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jan 29, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,183
Messages
5,623,238
Members
415,957
Latest member
Newguy1924

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