Exercises / Modern Excel: dynamic arrays · 7 of 12
Filter by two conditions, with a fallback
To require two conditions, multiply them: (A2:A9="East")*(B2:B9>100) is 1 only where both are TRUE. FILTER takes a third argument to show when nothing matches; without it you get #CALC!.
Your taskIn F4, use FILTER to return the rows of A2:C13 for the region in G1 with an amount of at least G2. If nothing matches, show "No match".
Pro
Unlock this exercise and every module after the basics.
Pro opens every exercise in every module, your review schedule, and the final test for the course certificate.
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Region | North | |||
| 2 | #3001 | North | $1,250.00 | Minimum | $1,250.00 | |||
| 3 | #3002 | South | $480.00 | Order | Region | Amount | ||
| 4 | #3003 | East | $2,210.00 | |||||
| 5 | #3004 | North | $760.00 | |||||
| 6 | #3005 | West | $1,890.00 | |||||
| 7 | #3006 | South | $3,120.00 | |||||
| 8 | #3007 | North | $2,475.00 | |||||
| 9 | #3008 | East | $395.00 | |||||
| 10 | #3009 | West | $1,040.00 | |||||
| 11 | #3010 | North | $1,985.00 | |||||
| 12 | #3011 | South | $1,500.00 | |||||
| 13 | #3012 | East | $820.00 |