Home › Practice questions › VLOOKUP › Question 8

Excel VLOOKUP Practice Question: Using VLOOKUP with MATCH to pick the column automatically, find the units for…

By Rinkesh Shah, founder of Cellgrids.

The question

Using VLOOKUP with MATCH to pick the column automatically, find the units for the product in B8 in the month typed in B7 and put it in B9.

The data

	A	B	C	D	E
1	Product	Jan	Feb	Mar	Apr
2	Laptop	42	38	51	47
3	Monitor	65	70	58	73
4	Mouse	150	142	160	171
5	Keyboard	120	105	134	98
6
7	Month	Mar
8	Product	Mouse
9	Units	?

The yellow cell is where your answer goes.

Show answer

Answer in B9: 160

How it works

Explanation: MATCH finds which column the month is in, and VLOOKUP uses that number as its column index. This avoids typing a fixed column number.
Syntax: =VLOOKUP(B8,A2:E5,MATCH(B7,A1:E1,0),FALSE)
MATCH("Mar",A1:E1,0) returns 4 because Mar is the 4th heading. VLOOKUP then returns the 4th column of the Mouse row: 160.

Try it yourself in the live sheet

Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.

Try this question in the live sheet →

VLOOKUP syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Looks up a value in the first column of a range and returns a value from another column, same row. Pulling an employee's salary into another sheet using their ID. Full VLOOKUP guide.