Using XLOOKUP to return multiple (variable) rows

[GraemeNZ]

Hi

We have a spreadsheet which records many data on separate tabs. The workbook is typically used for a year, so there are a few thousand rows overall. We extract some of these data onto a daily printout of salient information. The current formula finds the date specified in the list of data from a different tab, and returns as many entries as there are for that date. I find the current formula (based on old Excel) difficult to troubleshoot and correct when there is an error. I can easily create an XLOOKUP formula to pick up the data from the first row we need, but I'm struggling to see how to get the other rows to display.

Tab name of the other tab is "CIP". On the Daily Report tab, the date of the report required is in cell N2.

Old formula:

=IF(ISERROR(INDEX(CIP!$C$6:$P$63847,SMALL(IF($N$2=CIP!$M$6:$M$63849,ROW(CIP!$C$6:$P$63847)-MIN(ROW(CIP!$C$6:$P$63847))+1,""),ROW(CIP!A1)),COLUMN(CIP!A1))),"",INDEX(CIP!$C$6:$P$63847,SMALL(IF($N$2=CIP!$M$6:$M$63849,ROW(CIP!$C$6:$P$63847)-MIN(ROW(CIP!$C$6:$P$63847))+1,""),ROW(CIP!A1)),COLUMN(CIP!A1)))

New XLOOKUP formula which collects the relevant info - only for the first row! - and spills the other columns:
=XLOOKUP($N$2,CIP!$M$6:$M$63847,CIP!$C$6:$N$63847,"",0,1)

From reading help files, it seems I can use an array formula to look up an nth value, but each day there will be a different number of entries (rows). If there are none, the ISERROR returns a result of "", and I'd like to update this to IFERROR at the same time.

Unfortunately I'm not very good at structuring complex formulas and I end up getting tangled. Can anyone give me a formula for this?

First screenshot is the daily report showing the CIP information captured on the report using the old formula. 

Second screenshot shows the data from CIP tab with 10 entries on 1st Dec; 12 entries for 2nd Dec; 0 entries for 3rd Dec...

Can anyone assist with this?

GraemeNZ

[#2]

@GraemeNZ 

Try the FILTER function.

First,, clear the output range - the FILTER formula will automatically spill to as many rows as needed.

Then select the top left cell of the output, and enter the formula

=FILTER(CIP!C6:N63847, CIP!M6:M63847=N2, "")

[#3]

Hah! Genius, works like a dream, as simple and easy as I thought we should be able to do 🙂😄

For another part of the same report, I only want to report 2 columns, then miss one, then do the rest,.. So I simply adjust the array range to match the columns I want.

Thanks, GraemeNZ

[#4 · Accepted solution]

@GraemeNZ 

If you want to return non-adjacent columns, you can use another FILTER, or INDEX combined with SEQUENCE.

For example

=FILTER(FILTER(CIP!C6:N63847, CIP!M6:M63847=N2, ""), {1,1,0,1,1,1,1,1,1,1,1,1})

The 0 in 3rd place in {1,1,0,1,1,1,1,1,1,1,1,1} skips the 3rd column in C:N.

Or:

=FILTER(INDEX(CIP!C6:N63847, SEQUENCE(ROWS(CIP!C6:N63847)), {1,2,4,5,6,7,8,9,10,11,12}), CIP!M6:M63847=N2, "")

Here, {1,2,4,5,6,7,8,9,10,11,12} specifies the column numbers within C:N that you want to include.

You can also use this method to change the order in which the columns are returned - simply change the array {1,2,4,5,6,7,8,9,10,11,12}.

[#5]

@HansVogelaar, this Filter - Index - Sequence with the column number array is perfect for my needs. I've changed the best response to here.

Thanks again 😄