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 |