How to Use VLOOKUP and HLOOKUP in Excel

This guide explains VLOOKUP and HLOOKUP in plain, simple language, with worked examples built one step at a time rather than assuming prior formula experience. It's written for anyone learning these formulas for the first time, regardless of which language they usually work in day to day.

Both formulas do the same basic job - matching a value and returning related data - the only difference is whether your data is arranged in columns or rows.

VLOOKUP means "vertical lookup." It looks down a column for a value you give it, and once it finds a match, it returns a value from another column in that same row.

HLOOKUP works the same way, except it looks across a row instead of down a column. It's used when your categories are listed across the top instead of down the side.

1. What to search for - a value like an ID or a quarter label.
2. Where to search - the full data range, including the row or column with the values you want back.
3. Which row or column to return - counted from the start of the selected range.
4. TRUE or FALSE - almost always FALSE, meaning an exact match is required.

The most frequent mistake beginners make with either formula is forgetting to set the last argument to FALSE, which can cause Excel to quietly return an approximate match instead of flagging that no exact match exists.

Turbo Excel VLookup performs the same kind of matching without requiring either formula to be typed - you select the matching columns from dropdown menus and the app applies the match across the whole dataset automatically.

A surprising number of VLOOKUP problems turn out to be a data type mismatch rather than a logic error - a value stored as text in one column and as a number in the other will never match, even if they look identical on screen.

Before assuming the formula itself is wrong, select the lookup value's cell and check the small green triangle or the number alignment; numbers align right by default, and text aligns left, which is a fast way to spot the mismatch.

In a shared workbook, a bare VLOOKUP formula gives the next person no indication of what it's supposed to be matching or why. Adding a cell comment, or a short label in an adjacent column, explaining the source and purpose of the lookup saves significant time when the workbook is revisited months later or handed to a colleague.

This becomes even more important once a workbook has several lookups feeding into each other, since untangling a chain of undocumented formulas is far harder than reading a documented one.