+ Reply to Thread
Results 1 to 22 of 22

LOOKUP Values with Multiple Inputs

  1. #1
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    LOOKUP Values with Multiple Inputs

    Hi,

    I've been trying to work out a way to use one of the LOOKUP functions (or possible INDEX/MATCH) which allows me to select multiple lookup values from a list, and then search adjacent columns to where the formula sits and return the first value it finds from the list.

    To paraphrase my setup...

    TABLE A

    Fruit
    Orange
    Apple
    Banana
    Pear

    TABLE B
    Col A is where the formula sits
    Col B is a list of dates
    Col C - G are a list of different workers

    Then the cells in table B are populated (in this example) with values validated from a list which references Table A i.e. I can apply a fruit to a worked on a certain day.

    I want the formula in Col A to reference the values in Table A as a lookup, then search contents of the cells in the same row, columns C thru G for one of these values. When it finds one it returns that value into the cell with the formula in.

    I've tried XLOOKUP, VLOOKUP and INDEX MATCH but I can't work out a way to have multiple lookup values and for it to return the value it finds.

    Help much appreciated!

    Tim

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    Re: LOOKUP Values with Multiple Inputs

    Thanks Glenn. Please find an example spreadsheet attached for reference. It's been slimmed down somewhat but represents what I am trying to achieve. The cells highlighted yellow have been populated manually and is where I want the formula to sit.Example Lookup.xlsx

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    One way:
    =TEXTJOIN(,,,,C2:G2)

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    or a SPILL array (ensure the results column is BLANK):

    =BYROW(C2:G11,LAMBDA(x,TEXTJOIN(,,,,x)))

  6. #6
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    Re: LOOKUP Values with Multiple Inputs

    Thanks, can you explain the SPILL array further please. The TEXTJOIN unfortunately won't work for me as I only want 1 value to be returned, I also want it to ignore some other entries e.g. vegetable hence the need to reference values in table A. Updated spreadsheet is attached to add further context.

    Example Lookup v2.xlsx

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    Delete A2 to A11.

    In A2:
    =LET(a,'TABLE A'!A2:A5,BYROW(C2:G11,LAMBDA(x,CONCAT(IFERROR(INDEX(a,MATCH(UNIQUE(x,1),a,0)),"")))))
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    Re: LOOKUP Values with Multiple Inputs

    Thanks Glenn, that's some sort of wizardry right there! I've applied it to my table (which I can't upload due to sensitive data) and I'm having a #SPILL! returned. Do you know what could be causing it?

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    1. The column where you are expecting the results to be MUST be blank.

    2. Is it being applied in an structured table inside Excel. if so, it won't work. So... its it a structured table?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    So if it IS a structured table (nasty things!!), use this:

    =CONCAT(IFERROR(INDEX('TABLE A'!$A$2:$A$5,MATCH(UNIQUE(Table1[@[Date]:[Person E]],1),'TABLE A'!$A$2:$A$5,0)),""))
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    Re: LOOKUP Values with Multiple Inputs

    Nope, not structured. I tried to apply your formula to a new column, which removed the error but it doesn't pull in the data I need. All the cells remain empty. Am I able to send you a private link to this spreadsheet in question? Cheers

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    PM sent to you.

  13. #13
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: LOOKUP Values with Multiple Inputs

    which I can't upload due to sensitive data
    It is also possible to replace the sensitive data by random data.

  14. #14
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    Re: LOOKUP Values with Multiple Inputs

    Thanks both, I've simplified my existing spreadsheet and redacted the key info. Values in column A are manually added for now...as you can see I want the search in the adjacent rows to ignore values unless they are included in cells selected in the second tab (ideally I would have all the cells selected.

    As mentioned all I need is the first instance of one of the cells being found being returned.

    Excel Query.xlsx
    Last edited by timwagg; 12-06-2022 at 10:50 AM.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    Did you attache the correct file? Column A in Planning is blank - no expected results. And I'm not sure which is the equivalent of "Fruit" and "Vegetable".

  16. #16
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    Re: LOOKUP Values with Multiple Inputs

    Hi Glenn, yep that's the right file.

    Fruits are equivalent to the cells in the tab project (my worksheet has multiple categories of fruits). The vegetables in this case are the cells with XXXX in them in Planning. These vary in my worksheet but they the common fact is none of the text in those cells feature in the tab, project.

    Sorry, I don't think I am being of much assistance in helping me!

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    Ahhh. I didn't expect to have to scroll down 2 screens worth of blank cells to find anything...

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    So... the main difference that now seems to emerge is that the "allowed" responses are not a single list (column A, as previously), but anything from column A to column G. is that correct??

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    Delete everything from A6 downwards. In A6:

    =LET(a,TOCOL(PROJECT!A2:G6,1),BYROW(PLANNING!D6:P68,LAMBDA(x,CONCAT(IFERROR(INDEX(a,MATCH(UNIQUE(x,1),a,0)),"")))))
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    Re: LOOKUP Values with Multiple Inputs

    Cheers Glenn, I can't pretend to know what most of this means but it seems to work...nearly. I've applied it to my main sheet however I am getting '0' listed in cells where nothing is found rather than a blank cell. Is there an argument I need to add? I also noted than a 0 is appended to the cells where it does find a value too, something before, sometimes afterwards.

  21. #21
    Registered User
    Join Date
    12-06-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    28

    Re: LOOKUP Values with Multiple Inputs

    Quote Originally Posted by timwagg View Post
    Cheers Glenn, I can't pretend to know what most of this means but it seems to work...nearly. I've applied it to my main sheet however I am getting '0' listed in cells where nothing is found rather than a blank cell. Is there an argument I need to add? I also noted than a 0 is appended to the cells where it does find a value too, something before, sometimes afterwards.
    Ignore this, I worked it out. I have some blank cells in my reference array which added the zeros. Not sure why but by adding a x to each cell seemed to sort the problem. Thanks for all your help, a great resource. Will mark yours as the solution.

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: LOOKUP Values with Multiple Inputs

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ 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: 4
    Last Post: 06-15-2019, 12:16 AM
  2. Lookup and return values from word inputs and vice versa
    By ljwheeler2000 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-26-2019, 12:32 PM
  3. [SOLVED] Creating an invoice - multiple inputs to pull from multiple return values
    By grrlwhit03 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-04-2018, 02:46 PM
  4. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  5. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  6. [SOLVED] another lookup- lookup column against row inputs
    By opeyemi1 in forum Excel General
    Replies: 2
    Last Post: 05-02-2012, 07:39 PM
  7. Multiple horizantal lookup inputs
    By sctraffic in forum Excel General
    Replies: 1
    Last Post: 10-28-2011, 03:48 PM

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