Using dynamic column references in Google Sheets filtering formula

Other Questions Google Sheets
1

I’m trying to make my Google Sheets filter formula more flexible. Right now, it filters out empty cells in a specific column like this:

=FILTER('Data Input'!$A$3:$A$9781, NOT(ISBLANK('Data Input'!$X$3:$X$9781)))

But I want to be able to change the column (X in this case) based on a cell value. I tried using INDEX:

=FILTER('Data Input'!$A$3:$A$9781, NOT(ISBLANK('Data Input'!$INDEX(I13)$3:$INDEX(I13)$9781)))

This didn’t work though. It just gave me an error. Is there a way to use a cell value to dynamically set the column in my filter formula? I’m not sure how to make this work. Any ideas would be really helpful!

2

I’ve been working with Google Sheets for years, and I’ve found that using INDIRECT() can sometimes be slow for large datasets. Here’s an alternative approach that might be more efficient:

=FILTER(‘Data Input’!$A$3:$A$9781, NOT(ISBLANK(INDEX(‘Data Input’!$A$3:$ZZ$9781, 0, MATCH(I13, ‘Data Input’!$A$1:$ZZ$1, 0)))) )

This formula uses INDEX and MATCH to dynamically select the column based on the header in I13. It’s generally faster than INDIRECT and more flexible than OFFSET. The key is to expand the range to cover all possible columns (A:ZZ in this case).

One caveat: make sure your column headers are unique, or you might get unexpected results. Also, double-check that I13 contains the exact header text you’re looking for. If you run into any issues, let me know and I can help troubleshoot.

3

hey sophialee92, have u tried using INDIRECT() function? it might work for ur dynamic column reference. something like:

=FILTER(‘Data Input’!$A$3:$A$9781, NOT(ISBLANK(INDIRECT(“‘Data Input’!”&I13&“3:”&I13&“9781”))))

hope this helps! lemme know if u need more clarification

4

I’ve encountered a similar issue before. The INDIRECT function suggested by AdventurousHiker17 is indeed a good approach, but there’s another method you might find useful. Try using the OFFSET function combined with MATCH. Here’s an example:

=FILTER(‘Data Input’!$A$3:$A$9781, NOT(ISBLANK(OFFSET(‘Data Input’!$A$3,0,MATCH(I13,‘Data Input’!$1:$1,0)-1,9779,1))))

This formula assumes your column headers are in row 1. The MATCH function finds the column number based on the value in I13, and OFFSET uses that to create a dynamic range. It’s a bit more complex, but it can be more robust in certain scenarios. Give it a try and see if it suits your needs better.