VLOOKUP to a cell with comma separated values

[bambers3185]

I need to be able to do a lookup in Column D (Pipeline) to Column A (Customer Number) in Worksheet 2, to the cell in Column A (Customer NUmber) in Worksheet 3 to find the value in Column D (Amount).
I can do that if there is only a single value in Column A as in Cell A4 using the formula =VLOOKUP("*" & A4 & "*",Sheet3!A2:D5,4,FALSE) which give me the value 2,425, but need to be able to do a Lookup to a cell with comma separated values,

Worksheet 2

Worksheet 3

 

[jasonb75]

Trying to do that with a single cell formula would be near to, if not completely impossible. You would need to break the customer numbers down into individual cells first to have any realistic chance of making it work.

It would be a far better idea to improve the method of entry in the bottom sheet so that there is only one customer number per row.