Excel · Lookups

VLOOKUP & HLOOKUP: find a row, walk right

VLOOKUP is the most-asked Excel question in analyst interviews. This lesson gives you one picture to memorise. The four arguments follow from it.

Your win todayWrite VLOOKUP and HLOOKUP from memory, and explain the default-match trap before the interviewer asks.

The one picture

VLOOKUP does two moves. (1) It searches down the first column of the range you give it for your key. (2) It walks right N columns along that row and returns the cell it lands on. Counting starts at 1 for the key column itself.^[MS]

Step ① uses the first column of the range. Step ② uses the column index: 4 means "the 4th column of the range", which is D here, not "4 columns past A".

The four arguments

=VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
           what to find   where to look   which column back   FALSE = exact

Argument	Meaning	Example
`lookup_value`	The key you're searching for	`"E103"` or a cell like `G2`
`table_array`	The range. The key must be in its first column.	`$A$2:$D$6`
`col_index_num`	Which column of the range to return (key column = 1)	`4` → Salary
`range_lookup`	`FALSE`/`0` = exact; `TRUE`/omitted = approximate	`FALSE`

The trap interviewers probeIf you leave out the 4th argument, the default is approximate match (TRUE), not exact. Approximate match assumes the first column is sorted ascending and returns the largest value ≤ your key. On unsorted data, or for a key that doesn't exist, it can quietly return the wrong row instead of an error.^[MS] For IDs and names, always write FALSE.

When approximate match is right: bands

Grades, tax slabs and commission tiers. You want "which band does 73 fall into?" with no exact 73 in the table:

	G (Min score)	H (Grade)
2	0	F
3	40	C
4	60	B
5	80	A

=VLOOKUP(73, G2:H5, 2, TRUE) → B, because 60 is the largest value ≤ 73. This works only because column G is sorted ascending.

Lock the range with `$`

When you copy a formula down, relative references shift: A2:D6 becomes A3:D7 and your table slides away. $A$2:$D$6 is an absolute reference that stays put. Press F4 (Mac: ⌘T) on a reference to toggle the $ signs.

HLOOKUP: the same idea, turned sideways

HLOOKUP searches across the first row and then walks down N rows.^[MS] Use it when your headers run along the top, like months.

V = Vertical: search down a column. H = Horizontal: search across a row. In both, the index counts from the search line itself, which is 1.

Say it in the interview"VLOOKUP searches the first column of a range for a key and returns the value from the same row, N columns to the right. I always pass FALSE for exact match, because the default is approximate and can return a wrong row silently. Its limits: it can't look to the left, and the hard-coded column number breaks when someone inserts a column. That's why I'd use INDEX-MATCH or XLOOKUP."

Retrieval check

Use the employee table above. Answer from memory before you scroll up.

What does =VLOOKUP("E102", A2:D6, 2, FALSE) return?

You omit the 4th argument. What does VLOOKUP do?

Your key (Name) is in column B, and you need EmpID from column A. Why does VLOOKUP struggle?

HLOOKUP searches for the key in the …

Why write $A$2:$D$6 instead of A2:D6?

Type it

Cell G2 holds E105. Write a formula that returns that employee's department.

Using the HLOOKUP sheet above, return March Sales.

Your turn

Do these in Excel or Google Sheets. Your ticks are saved in this browser.

Type the 5-row employee table into A1:D6.
Write =VLOOKUP("E103",$A$2:$D$6,4,FALSE). Then change 4 → 2 → 3 and predict each result before pressing Enter.
Look up "E999" without FALSE. You'll get Fatima's row back for an ID that doesn't exist. Add FALSE and watch it become #N/A.
Build the grade-band table and run =VLOOKUP(73,G2:H5,2,TRUE). Then shuffle the rows and watch it break.
Copy the employee table → Paste Special → Transpose. Get Meera's salary with HLOOKUP.
Close this page and say the "Say it in the interview" answer out loud from memory.

Go deeper

Primary source: Microsoft Support: VLOOKUP function. It's the official reference, and the video at the top is 3 minutes. Also: Look up values with VLOOKUP, INDEX, or MATCH.

💬 Stuck or curious? Ask your teacher (Claude). For example: "why does approximate match need sorting?" or "quiz me on VLOOKUP again".