Forum Discussion
Solved
Using XLOOKUP to return multiple (variable) rows
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 pri...
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}.
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, "")
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, GraemeNZIf 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}.
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 😄
