# Lookup ID and if cell in row contains something show the title of that column

1. ## Lookup ID and if cell in row contains something show the title of that column

Firstly, I hope I'm posting this in the right place but I have a formula I'm struggling to build in Google Sheets. I think it's a combination of LOOKUP and IF functions but I'm not sure how to execute it.

The link to the sheet is attached.

What I'm trying to do is lookup product IDs from a supplier and use that to find the product's allergens, which are then combined in my Catalog sheet.

E.g.

Sheet - Catalog
Cell - K2

LOOKUP the 'sku' (A2) in 'All Products' sheet & IF "Does Contain" is visible in 'All Products' O3:Y3 show 'All Products' O2:Y2 seperated by comma

Result in K2 would look like: Sesame,Soya,Nuts

Idealy this would be possible as an array so that it then copied down for all products added to the 'All Products' sheet in the future.

Any help would be much appreciated.

2. ## Re: Lookup ID and if cell in row contains something show the title of that column

This sort of concatenation is a very common request and is usually solved via VBA as it usually consists of an increasing number of columns, but as you have a fixed number of Allergens (16 columns O to Y - and hopefully this will not increase) it is possible to solve it via a formula. However, I can only see a formula being possible by using 16 IFs, one behind the other in one formula resulting in a value from O2:Y2 or a null which is not very practical e.g.

etc
for 16 columns

Then remove the rightmost comma of the result so you dont have "Sesame, Soya, Nuts,"

Cant see an easy way of doing this via formulas.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1