INDEX and MATCH on two conditions
An Excel formula exercise from Formuladrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.
The task
Return the units for the region in F1 and the product in F2, in F3. Use INDEX and MATCH on both conditions at once, without adding a helper column.
What this exercise teaches
Multiplying two comparisons gives a column of 1s and 0s, and MATCH looks for the 1. This is the standard two-criteria lookup when a helper key column is not allowed. In Excel 2019 and older it has to be confirmed with Ctrl+Shift+Enter.
A hint if you get stuck
(A2:A7=F1)*(B2:B7=F2) is 1 only on the row where both conditions are true. MATCH(1, that product, 0) finds the row.
Try it now, free, no account needed
Your first 15 exercises on Formuladrill 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
- Look left with INDEX and MATCH
- When the code does not exist
- Tax bracket lookup
- Finishing position
- Price grid
- Return from the left
- Price list into an order
- A lookup inside IFERROR
- Who sold the most
- A column number that moves
- Commission tiers with XLOOKUP
- The most recent price
- Everyone in one department
- Third place