CODEWITHSUNDEEP
excelvlookup
Sunetra Banerjee Bass
Sunetra Banerjee Bass

Reputation: 3

Lookup of comma separated value in Excel

Would appreciate if someone could help with this excel formula:

Here's what I need:

In my excel sheet, Tab1 has this:

Col A     Col B
Red        Lookup Formula to show 'Red' or 'Not Available'
Green     Lookup Formula to show 'Green' or 'Not Available'
Blue        Lookup Formula to show 'Blue' or 'Not Available'
Yellow     Lookup Formula to show 'Yellow' or 'Not Available'
Purple     Lookup Formula to show 'Purple' or 'Not Available'

In Tab2, Col K, I've this:

Col K
Red
Red, Green
Orange, Blue

Now, formula in Col B in Tab1 match against the values present in Col K of Tab2. And as soon as a match is found (does not matter if the match is found multiple times), should automatically set the value in Col B of Tab1

Otherwise, just put 'Not Available'

Col A     Col B
Red         Red
Green       Green
Blue         Blue
Yellow       Not Available
Purple       Not Available

Please note:

Previously, my Tab2 had no comma separated value(csv), just 1 value in 1 cell and at that time, this formula worked fine for me:

=IFNA(VLOOKUP(Col1, 'Tab2'!K:K, 1, FALSE), "Not available")

Upvotes: 0

Views: 1569

Answers (1)

Chad Portman
Chad Portman

Reputation: 1226

Assuming that Tab 1 A1 is "Red" you can place the below formula in B1 and copy down.

=IF(COUNTIF('Tab2'!K:K,"*"&A1&"*")>0,A1,"Not Avaiable")

Upvotes: 0

Related Questions