I’m working with a large dataset that has around 250 regions and 80 different items. Right now my formula works by checking if a region has “yes” in a column and then it shows the price for that item in that region.
I want to modify this so I can set a minimum threshold for each item in each region. The formula should check if the value meets or exceeds the minimum threshold before displaying it.
Currently I’m using this formula: =arrayformula( if( transpose(Settings!C16:C262) = "yes", Data!I1:JU200, iferror(1/0) ) )
I need to figure out how to do this in one formula because I have more than 50 partner worksheets that need this change. I was thinking something like: =query( Data!I1:JU200, "select * where * >'"&transpose(Settings!C16:C262)&"')")
I also tried: =arrayformula( if( Data!I1:JU200 > (transpose(Settings!C16:C262)), Data!I1:JU200, iferror(1/0) ) )
And this approach with individual columns: =query( Data!I1:I200, "select * where I > '"&Settings!$C$16&"' ",1 )
None of these attempts worked properly. How can I create a single formula that compares values against minimum thresholds?
I hit the same issue with sales data across territories. Your arrayformula isn’t working because there’s a dimension mismatch between your data range and threshold values. I fixed mine using MAP and LAMBDA functions. Try =arrayformula(if(and(isnumber(Data!I1:JU200), Data!I1:JU200>=transpose(Settings!C16:C262)), Data!I1:JU200, "")) instead. You need your Settings range to match your data range dimensions exactly. If Settings!C16:C262 has your thresholds, reshape it to match your 200x80 data structure. Also check that all thresholds are actually numbers, not text - that’ll break the comparison without throwing an error.
The issue is transpose(Settings!C16:C262) creates a horizontal array but your data range is vertical - they don’t match up. I ran into this same thing and fixed it by restructuring the comparison logic. Don’t try to force arrayformula to handle mismatched dimensions. Try FILTER with multiple conditions instead: =filter(Data!I1:JU200, (Data!I1:JU200>=Settings!C16:C262)*(Data!I1:JU200<>"")). You’ll still need your threshold range to align with your data structure though. If your thresholds are stored vertically but need to apply horizontally across items, create a helper range that repeats each threshold across the columns where it should apply. Query won’t work here since it can’t handle dynamic threshold comparisons across multiple columns at once.
Try =arrayformula(if((Data!I1:JU200>=transpose(Settings!C16:C262))*(Data!I1:JU200<>""), Data!I1:JU200, "")) - it’ll compare each cell against your thresholds and only show values that meet the criteria. Just make sure your threshold range matches the data dimensions.