I’m working with a large dataset that has about 250 regions and 80 different items. Right now my formula works by checking if a region is marked as ‘active’ and then it shows the pricing data for that item in that region. I want to modify this so I can set minimum threshold values for each item per region. The formula should check if the actual value meets or exceeds the minimum threshold before displaying the data. My current working formula looks like this: =arrayformula( if( transpose(Settings!C20:C270) = "active", Data!J1:KZ250, iferror(1/0) ) ) I’m struggling to figure out how to incorporate the threshold comparison into a single formula. I need to update this across more than 50 different worksheets so a single formula solution would be ideal. I attempted something like: =query( Data!J1:KZ250, "select * where * >'"&transpose(Settings!C20:C270)&"')") Also tried: =arrayformula( if( Data!J1:KZ250 > (transpose(Settings!C20:C270)), Data!J1:KZ250, iferror(1/0) ) ) And this approach for individual columns: =query( Data!J1:J250, "select * where J > '"&Settings!$C$20&"' ",1 ) None of these approaches worked properly. Any suggestions on how to create a formula that compares my data range against minimum threshold values?
try =arrayformula(if((transpose(Settings!C20:C270)=="active")*(Data!J1:KZ250>=transpose(Settings!D20:D270)), Data!J1:KZ250, "")) with your thresholds in column D. both conditions need to be true for it to work.
Running complex formulas across 50+ worksheets is a maintenance nightmare. Been there - Google Sheets formulas become impossible to debug at that scale.
Skip the array formulas entirely. Automate it instead. Build a workflow that reads your sheets, runs your threshold logic through a simple script, then writes the filtered results back.
Best part? Handle all 50 worksheets in one go. No more copy-pasting formulas or dealing with dimension mismatches. Write your logic once, let it run everywhere.
I’ve built this exact setup for filtering tasks. Way more reliable than cramming everything into one formula that breaks when someone adds a row.
You can build Google Sheets automation like this at https://latenode.com
Your problem is dimension mismatch and bad query syntax. Here’s what works: =arrayformula(if(and(transpose(Settings!C20:C270)="active", Data!J1:KZ250>=transpose(Settings!E20:E270)), Data!J1:KZ250, "")) with your thresholds in column E. Make sure your threshold column has exactly the same rows as your data range - that’s where most people mess up. Use empty string “” instead of iferror(1/0) - it’s way cleaner when conditions fail. Also check that Settings!E20:E270 has actual numbers, not text. Text thresholds will break your comparisons every time.
Your second attempt is close but the structure needs work. Don’t try to multiply conditions directly - wrap each one separately and handle the arrays properly. Try =arrayformula(if((transpose(Settings!C20:C270)="active")*(Data!J1:KZ250>=transpose(Settings!F20:F270)), Data!J1:KZ250, "")) but double-check that your threshold range in column F matches your data dimensions exactly. I ran into the same issue when my threshold column was shorter than my data range. Google Sheets fills missing values with zeros, which breaks the comparison completely. Make sure Settings!F20:F270 has valid numbers for every row in your data. Also check that both ranges start and end at the same spots. The transpose function works fine for your setup, but if the dimensions don’t align perfectly, array formulas will fail.