How to VLOOKUP one value out from a cell with multiple values separated by comma?

Question

For example, I am trying to get the Project ID of the SKU in C2. However, in A:A, there are multiple values in a cell which are separated by a comma. A simple VLOOKUP won't work.

Are the project ID's only numeric? I.e. will you ever need to return that N/A in B6? — BigBen
@BigBen ProjectIDs are only numeric. Forgive me for the N/A. I will delete the N/As — Jezun
Are they actual numbers too? They are left aligned which makes me think they're text. — BigBen

BigBen BigBen · Accepted Answer · 2019-12-19T19:32:52

If the Project IDs are numeric and are true numbers, then use SUMIF with wildcards:

=SUMIF(A:A,"*"&C2&"*",B:B)

If the project IDs aren't necessarily numeric, you could use an approach like the following:

=INDEX($B$2:$B$14,SUMPRODUCT(--ISNUMBER(SEARCH(C2,$A$2:$A$14))*ROW($A$1:$A$13)))

How to VLOOKUP one value out from a cell with multiple values separated by comma?

2 Answers