I want to make a dynamic list using filter and offset. I want to search a selected column with the search criteria.
The below formula works, but it's not dynamic.
=FILTER(Sheet1!$B$6:$AG$600,ISNUMBER(SEARCH($B$2,Sheet1!$D$6:$D$600)))
| Column | Building # ($B$1) |
| Search | 102 ($B$2) |
But when I want to make it dynamic with offset, it doesn't work.
=FILTER(Sheet1!$B$6:$AG$600,ISNUMBER(SEARCH($B$2,OFFSET(Sheet1!$B$6,0,MATCH($B$1,Sheet1!$B$6:$AG$6,0)-1,COUNTA(Sheet1!$C:$C)))))
I've tried the formula below and it works and spills TRUE and FALSE like it should.
=ISNUMBER(SEARCH($B$2,OFFSET(Sheet1!$B$6,0,MATCH($B$1,Sheet1!$B$6:$AG$6,0)-1,COUNTA(Sheet1!$C:$C))))
Is there another way to do this or does offset not work with the filter function?
Thanks in advance!
Try:
=FILTER(Sheet1!$B$6:$AG$600,ISNUMBER(SEARCH($B$2,INDEX(Sheet1!$B$6:$AG$600,0,MATCH($B$1,Sheet1!$B$6:$AG$6,0)))))
I was still getting an #VALUE error with that. Thank you though.
But I did figure out my issue with the offset. The range didn't have the same amount of rows. I had the offset go down 1 row, which threw everything off.
Here's the corrected formula with the offset. I corrected the row part of the offset.
=FILTER(Sheet1!$B$7:$AG$600,ISNUMBER(SEARCH($P$4,OFFSET(Sheet1!$B$6,0,MATCH($P$3,Sheet1!$B$6:$AG$6,0)-1,COUNTA(Sheet1!$C:$C)))),"Not Found")
Thanks again for your time!
