+ 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
    Excel 2019
    Posts
    7,064

    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)

Similar Threads

  1. Replies: 8
    Last Post: 02-07-2019, 05:56 AM
  2. Lookup Column Title Using 2 Variables
    By stockmonkeys in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-23-2016, 12:38 PM
  3. [SOLVED] Make a cell with a value in it show a Title instead
    By Petsi in forum Excel General
    Replies: 8
    Last Post: 10-02-2014, 10:46 AM
  4. Show full path title in title bar?
    By Nor in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 04-11-2014, 02:26 PM
  5. Find a column by title even if its been moved and show contents on another sheet
    By KAPearson in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-26-2012, 06:10 PM
  6. Column Title Lookup
    By creativefusion in forum Excel General
    Replies: 2
    Last Post: 09-24-2009, 08:36 PM
  7. Show sheet title/name in cell
    By age in forum Excel General
    Replies: 4
    Last Post: 01-16-2007, 06:22 AM

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