Lookup on two keys
A Google Sheets formula exercise from Sheetsdrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.
The task
The price depends on both the plan and the billing period, and each pair appears once. Return the price for the plan in F1 and the period in F2. Answer in F3.
What this exercise teaches
VLOOKUP and XLOOKUP take one key. FILTER with two conditions returns the row that matches both. The INDEX and MATCH version multiplies the two tests, so only the matching row gives 1.
A hint if you get stuck
FILTER(C2:C7, A2:A7=F1, B2:B7=F2) keeps the one row where both tests pass. INDEX with MATCH(1, (A2:A7=F1)*(B2:B7=F2), 0) works too.
Try it now, free, no account needed
Your first 15 exercises on Sheetsdrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every exercise, timed mode and the certificate.
Open this exerciseMore exercises in Lookups
- Find the price
- Stock on hand
- Look left with XLOOKUP
- A friendly "Not found"
- INDEX and MATCH
- Zero instead of #N/A
- Shipping rate by weight band
- Row and column lookup
- Latest status in a log
- Commission rate tier
- Targets laid out in a row
- Lookup by the start of a name
- Next sailing on or after a date
- Place in the queue
- Is the code on the list?