Vlookup with singular or multiple lookups

[ADSkinner]

Good morning....

I have been working on two vlookup formulas to go along with the data as shown.

Column B Column F Column G
1699944 1699942, 1699943, 1699944 40122036
1697497 1697497 40013846

In Column C, row 1 I have formula: =VLOOKUP("*" & B11 & "*",$F$11:$G$12,2,FALSE).

This works and returns the value of Column G by doing the lookup comma separated data in Column F.

However it does not work if Column F is only a singular value, so in Column C, row 2 I have formula: =IF(VLOOKUP(B12,F11:G12,2)=VLOOKUP(B12,F11:G12,2),VLOOKUP(B12,F11:G12,2),VLOOKUP("*"&B12&"*",$F$11:$G$12,2,FALSE)).

This works and returns the value of Column G by doing the lookup, but won't work if I add comma separated data in Column F.

With what I have, am I on the right track in getting it to return the correct value whether it has one, two, three, or many lookup values, by somehow combining these two formulas?

Thanks in advance for taking a look and offering a helping hand.

 

[Fluff]

Try
=VLOOKUP("*" & B11 & "*",TEXT($F$11:$G$12,"0"),2,FALSE)

This need to be confirmed with Ctrl Shift Enter, not just enter. If done corectly the formula will be wrapped in {}

 

[shift-del]

Hi

=LOOKUP(9^99,SEARCH(B11,$F$11:$F$12),$G$11:$G$12)