Usually, in Google Sheets, when we use a FILTER formula with an absolute range for the data (like $A$2:$A$10) and a relative reference for the criteria (like B2:B10), the criteria column updates correctly when dragging across columns, but the column stays the same when dragging down.
This means that as you copy the formula down, it continues to reference the same criteria column instead of moving to the next one. Let’s see how to fix this with a simple example.
Example Data
I have a small student marks table with the following columns:
- Student Name
- Maths
- Chemistry
- Physics
- General Knowledge
Now, I want to filter out the poor performers (those who scored less than 20 out of 50) for each subject.
- First row → poor performers in Maths
- Next row → poor performers in Chemistry
- Next → Physics
- and so on.
Step 1 – Normal Filter Formula
For Maths, the basic formula is:
=IFNA(FILTER($A$2:$A$10, B2:B10<20, B2:B10<>""))
This works fine. If I copy-paste to the right, it picks Chemistry, Physics, etc. But if I drag down, it stays stuck on the Maths column.
⚠️ Also, if the FILTER returns multiple values, dragging the formula down can cause a #REF! error.
Step 2 – Use TRANSPOSE
Next, I want the results to show in one row, so I wrap the formula with TRANSPOSE:
=TRANSPOSE(IFNA(FILTER($A$2:$A$10, B2:B10<20, B2:B10<>"")))
This just changes the layout, but the column reference still doesn’t shift when dragging down. However, using TRANSPOSE also solves the #REF! error that can occur when FILTER returns multiple values.
Step 3 – Shift the Column When Dragging Down
Here comes the trick. Instead of locking B2:B10, I use INDEX to dynamically point to the correct subject column:
=TRANSPOSE(
IFNA(
FILTER(
$A$2:$A$10,
INDEX($B$2:$E$10, 0, ROW(A1))<20,
INDEX($B$2:$E$10, 0, ROW(A1))<>""
)
)
)
How it works:
ROW(A1)returns 1 in the first row, 2 in the next, etc.- INDEX then uses that number to pick the subject column (Maths = 1, Chemistry = 2, etc.).
- That way, as you drag the formula down, the criteria column shifts automatically.
Step 4 – Automate with BYCOL (Optional)
If you don’t want to drag formulas at all, you can use the BYCOL function:
=TRANSPOSE(
BYCOL(
B2:E10,
LAMBDA(col, IFNA(FILTER(A2:A10, col<20, col<>"")))
)
)This applies the same filter logic to each subject column automatically. The TRANSPOSE just makes the results easier to read row by row.
Why This Matters
Shifting the column in a Filter formula when dragging down is handy when:
- You need to analyze multiple subjects or categories row by row.
- You don’t want to keep rewriting or manually editing formulas.
- You want a neat layout where each row represents one subject’s filtered results.
