Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
Lookups on a combined key. When one column isn’t unique, join two of them with & and look up the joined value. In XLOOKUP the lookup array can be joined too: A2:A13&B2:B13 builds the key for every row on the fly, with no helper column. Join both sides in the same order.
=XLOOKUP(H2&I2,$A$2:$A$50&$B$2:$B$50,$D$2:$D$50) a price by product and size
The planning system exports Q2 targets as one long list: one row per month and region. Neither the month nor the region alone identifies a row, so a normal lookup returns the wrong target.
In G2:G6, return the target from C2:C13 for the region in column E and the month in column F, matching both month (A2:A13) and region (B2:B13) in one lookup. Write it once in G2 and fill down.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Region | Target | Region | Month | Target | ||
| 2 | 2025-04-01 | North | 6,847 | South | 2025-05-01 | |||
| 3 | 2025-04-01 | South | 8,558 | West | 2025-04-01 | |||
| 4 | 2025-04-01 | East | 4,893 | North | 2025-06-01 | |||
| 5 | 2025-04-01 | West | 11,503 | East | 2025-04-01 | |||
| 6 | 2025-05-01 | North | 6,757 | West | 2025-06-01 | |||
| 7 | 2025-05-01 | South | 8,684 | |||||
| 8 | 2025-05-01 | East | 5,655 | |||||
| 9 | 2025-05-01 | West | 11,159 | |||||
| 10 | 2025-06-01 | North | 6,329 | |||||
| 11 | 2025-06-01 | South | 8,553 | |||||
| 12 | 2025-06-01 | East | 4,827 | |||||
| 13 | 2025-06-01 | West | 11,748 | |||||
| 14 | ||||||||
| 15 | ||||||||
| 16 |
=IF(B2>=1.5,SUM(C2:C9),0)Language
Full plan
Excel ✓ · SQL and Power BI also included
Cancel anytime · 14-day full refund · Secure payment by Stripe