Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
A lookup inside a lookup. Sometimes the answer sits two tables away: order, then customer, then region, then manager. Put one lookup inside another. The inner one finds the region, and the outer one uses that region to find the manager. Test the inner one on its own first.
=XLOOKUP(XLOOKUP(A2,D2:D9,E2:E9),H2:H5,I2:I5)
Each November order needs its regional manager for the follow-up call. The order list only has the customer ID, and the customer extract (F:H) was taken in early May, before the newest customers signed up.
In C2:C11, return the regional manager for each order: find the customer’s region in F1:H10, then that region’s manager in J1:K5. When the customer isn’t in the extract, show "Check customer".
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Customer ID | Manager | Customer ID | Customer | Region | Region | Manager | |||
| 2 | 50917 | 1099 | 1017 | Cedar Realty | North | North | Elena Petrova | ||||
| 3 | 50918 | 1047 | 1027 | Granite Accounting | West | South | Marcus Hill | ||||
| 4 | 50919 | 1027 | 1047 | River Dental | East | East | Priya Raman | ||||
| 5 | 50920 | 1093 | 1050 | Granite Fitness | South | West | Daniel Ortiz | ||||
| 6 | 50922 | 1002 | 1052 | Harbor Hotels | West | ||||||
| 7 | 50923 | 1071 | 1059 | Oak Studios | West | ||||||
| 8 | 50924 | 1052 | 1071 | Atlas Bakery | South | ||||||
| 9 | 50926 | 1017 | 1093 | Copper Dental | West | ||||||
| 10 | 50927 | 1059 | 1099 | Blue Accounting | North | ||||||
| 11 | 50928 | 1050 | |||||||||
| 12 | |||||||||||
| 13 | |||||||||||
| 14 |
=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