Sheetsdrill

Level 3: Lookups Exercise 49 of 92

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.

Functions used: FILTER

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 exercise

More exercises in Lookups