+ 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
    09-18-2019
    Location
    Brighton
    MS-Off Ver
    Google Sheets
    Posts
    1

    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.

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

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    MS Office Excel 2007
    Posts
    6,857

    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)&",","")&
    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.
    Last edited by Special-K; 09-18-2019 at 06:04 AM.
    Regards
    Special-K

    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

Bookmarks

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