Forum Discussion
Help with filtering rows within index formula
Hi all,
I am using the following to show the data in Sheet2! columns with headers (Sheet2!A1:T1) matching A3:N3
=IF(INDEX(Sheet2!$A$2:$T$5000,SEQUENCE(ROWS(Sheet2!$A$2:$T$5000)),XMATCH($A$3:$N$3,Sheet2!$A$1:$T$1))=0,"",INDEX(Sheet2!$A$2:$T$5000,SEQUENCE(ROWS(Sheet2!$A$2:$T$5000)),XMATCH($A$3:$N$3,Sheet2!$A$1:$T$1)))
I want to adjust this to only show rows for which Sheet2!I2:I5000=B1 (where B1 will be a data validated dropdown of unique values in Sheet2!I2:I5000).
How can I best achieve this?
- FILTER() function may easier. Please attach/share a sample file.
As already mentioned, filter function would be most suitable.
But if you want something in this direction, here is a suggestion.
=IFERROR(INDEX(Sheet2!$A$2:$T$5000,SMALL(IF(Sheet2!$I$2:$I$5000=B1,ROW(Sheet2!$I$2:$I$5000)ROW(Sheet2!$I$2)+1),ROWS($A$1:A1)),XMATCH($A$3:$N$3,Sheet2!$A$1:$T$1)),"")
