This post demonstrates how we could vlookup each comma separated value in one cell.
To understand it further let’s refer to a scenario here, we have a lot of comma separated values in one cell and we want to vlookup all the values which are there in one cell in excel.
In cell C2 we want the value of account id corresponding to Row-ID “3-ABC” and the values for all the row id’s are in table ranging from F1:G3 and as you could see all the lookup values are in one cell separated by comma.
We could use a simple trick to vlookup if all the look up values are in one cell.
Enter the formula =VLOOKUP(“*” & B2 & “*”,F1:$G$3,2,FALSE) in the cell C2.
The trick to look up all the values in one cell separated by comma is to use wildcard character “*” in the vlookup formula, which denotes 0 or more character.
Our formula is traversing through all the values separated by comma in cell F1 and returning the corresponding values from G2, the formula it does not bother about the presence of multiple commas separated values that we want look up for , it will return the value based on the the string that is available in the column B.
As you could see, I have inserted another value “4-XYZ” and it returns me its corresponding accounting ID.
You could build your regular expression based on any type of patterns that you might have in one cell and want to vlookup for all of them.
Hope this helped.
