Excel track · Exercise 37 of 86 Paid
Two-way lookup with VLOOKUP and MATCH
Return one cell of a grid from a row key and a column key.
Advanced
Quarterly units by product
Read-only data · answer in C6
In C6, return the units for the product in A6 and the quarter in B6, using VLOOKUP and letting MATCH find the column.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Q1 | Q2 |
| 2 | Gizmo | 120 | 140 |
| 3 | Widget | 90 | 110 |
| 4 | Sprocket | 60 | 75 |
| 5 | Flange | 150 | 165 |
| 6 | Widget | Q2 |
Reference answer
What it does
MATCH finds Q2 in the header A1:C1 and returns 3, the third cell of that range. VLOOKUP takes that 3 as its column number, finds Widget on row 3 of A1:C5, and returns the third value there, 110.
No digit in the formula describes the table. Change B6 to Q1 and the answer becomes 90 with no edit; insert a quarter in the middle and the formula follows it. Type the 3 yourself and neither holds.
The two ranges have to start on the same column. MATCH counts from the first cell it is given, while VLOOKUP counts from its table's left edge. Point MATCH at B1:C1 and its 2 lands on Q1.
Syntax
=VLOOKUP(product, table, MATCH(quarter, header_row, 0), FALSE)Worked example
On Quarterly units by product, =VLOOKUP(A6,A1:C5,MATCH(B6,A1:C1,0),FALSE) returns 110.
=VLOOKUP(A6,A1:C5,MATCH(B6,A1:C1,0),FALSE)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | The quarter is not in the header, or the product is not in the first column. |
#VALUE! | The MATCH position was joined to text, so VLOOKUP was handed a column number it cannot count with. |
#REF! | MATCH's column number is wider than A1:C5, which happens when its header starts before the table does. |
At work
Units by product and quarter, headcount by team and month: the column you need is itself a lookup.