Lookup Question - Possible to match if the cell *contains* the lookup value?

[AdamParker]

Hello all - 2nd post today, I had such good help with the first one I thought I'd try again. Thanks again!

I'm trying to bring in some values into my data set.
I have a column which I'm using to lookup that contains a ref#.

For example,
AB123
AB128
AC234
AJ542
etc...

In my lookup table I don't always have an exact 1:1 match, and some cells contain more than one ref#.

For example,
AB123
AB128, AB234 (this is the value in one cell, comma-seperated)
AC234, AJ542

Right now if I lookup AB128, I get #N/A as there isn't an exact match with the entire cell.
Is there a way I can lookup AB128 within the cell, and if it is present, return the value I need?

The ref# is unique and will only appear in the table once.
Any help would be appreciated.

Thanks!

[NBVC]

With VLOOKUP and INDEX/MATCH you can use wildcards.

e.g.

=VLOOKUP("*"&A1&"*",X:Y,2,FALSE)

will lookup up the value in A1 in column X and find it amongst the strings whether it is the beginning, end or middle of the strings within that range and return the corresponding Y column value.

[AdamParker]

Brilliant - thank you!