SOLVED

Help pulling information from one tab to another.

Copper Contributor

I am trying to get information to pull from one tab to another and having issues. I believe it is because there can be multiple responses. I am trying to pull information based on table number. I have already used the following formula to pull information from this tab to a different one, but it doesn't work for this situation because multiple lines have the same table number.

 

=IF(ISERROR(XLOOKUP(1,'Master Seating'!H$2:H$159,'Master Seating'!K$2:K$159,)),"",(XLOOKUP(1,'Master Seating'!H$2:H$159,'Master Seating'!K$2:K$159,))) 

 

Is there a way to edit this formula to work or another formula that I should be using. 

 

I have attached screen grabs of the workbooks. From the first workbook I want to pull the names and allergies into the second workbook based on Table number. 

Workbook1

Master List.png

Workbook 2

Dietary.png

 

 

3 Replies
best response confirmed by Lhelgert (Copper Contributor)
Solution

@Lhelgert 

Excel's lookup functions (LOOKUP, VLOOKUP, XLOOKUP) are designed to return a single value (or with adjacent values, in some cases).  To return data from (possibly) more than one row, you should use Excel's FILTER function.  See the attached workbook.

@Lhelgert 

 

Let me refer you to the FILTER and SORT functions (which will need Excel 2021 or newer). Here, too, is a video that Microsoft created when introducing those functions.

 

If you need more specific help, please come back after posting a copy of that sample worksheet on OneDrive or GoogleDrive and pasting a link here that grants access.

 

(Posting images alone requires us to recreate the file; it's easier for us to help you if you post an actual workbook that represents the reality you're dealing with, arrayed as you have it.)

Thank you! this is exactly what I was hoping for.
1 best response

Accepted Solutions
best response confirmed by Lhelgert (Copper Contributor)
Solution

@Lhelgert 

Excel's lookup functions (LOOKUP, VLOOKUP, XLOOKUP) are designed to return a single value (or with adjacent values, in some cases).  To return data from (possibly) more than one row, you should use Excel's FILTER function.  See the attached workbook.

View solution in original post