SOLVED

# Help pulling information from one tab to another.

Copper Contributor

# Help pulling information from one tab to another.

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

Workbook 2

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

# Re: Help pulling information from one tab to another.

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.

# Re: Help pulling information from one tab to another.

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

# Re: Help pulling information from one tab to another.

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

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

# Re: Help pulling information from one tab to another.

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.