+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Google Sheets

    Question 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.


    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    London, England
    MS-Off Ver
    MS Office Excel 2007

    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.

    IF(INDEX(O3:Y3,MATCH(A2,'All products!A$3:A$2000,0),1)="Does Contain", INDEX(O2:Y2,MATCH(A2,'All products!A$3:A$2000,0),1)&",","")&
    IF(INDEX(O3:Y3,MATCH(A2,'All products!A$3:A$2000,0),2)="Does Contain", INDEX(O2:Y2,MATCH(A2,'All products!A$3:A$2000,0),2)&",","")&
    IF(INDEX(O3:Y3,MATCH(A2,'All products!A$3:A$2000,0),3)="Does Contain", INDEX(O2:Y2,MATCH(A2,'All products!A$3:A$2000,0),3)&",","")&
    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.
    Last edited by Special-K; 09-18-2019 at 06:04 AM.

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread


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