CODEWITHSUNDEEP
excelexcel-formulavlookup
Jezun
Jezun

Reputation: 77

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

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

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.

VALUES

Upvotes: 0

Views: 3587

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Or, try this :

=LOOKUP(1,-SEARCH(C2,A$2:A$21),B$2:B$21)

Upvotes: 1

BigBen
BigBen

Reputation: 49998

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)))

enter image description here

Upvotes: 1

Related Questions