VLOOKUP and HLOOKUP solve the exact same problem - matching a value and returning data from a related row or column - but they're built for two different data layouts. VLOOKUP searches down a column, while HLOOKUP searches across a row.
This page covers both with worked examples, so it's clear which one fits a given spreadsheet's layout before writing either formula.
If your data is organized with categories running down the left side and records listed in rows underneath, use VLOOKUP. If your data instead has categories running across the top row, with values listed underneath each one, use HLOOKUP - it's purely a question of layout, not which is "better."
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| E101 | Asha Rao | Marketing | 54000 |
| E102 | Vikram Shah | Finance | 61000 |
| E103 | Priya Nair | Sales | 48000 |
Here quarterly figures run across the top row instead of down a column:
| Metric | Q1 | Q2 | Q3 |
|---|---|---|---|
| Revenue | 210000 | 245000 | 238000 |
| Expenses | 150000 | 162000 | 158000 |
The third argument, row_index_num, counts rows downward instead of columns across - row 2 here is the Revenue row.
Most real-world spreadsheets are organized with records running down rows rather than across columns, which is why VLOOKUP tends to come up far more often in practice. HLOOKUP is mainly useful for summary tables, dashboards, or data that was exported in a transposed layout.
Turbo Excel VLookup is built around the more common column-based layout that VLOOKUP handles, matching an entire dataset at once through its dropdown interface rather than requiring either formula to be written manually.
A worksheet with thousands of VLOOKUP formulas recalculating against a large table_array can slow Excel down noticeably, especially if the range isn't locked and Excel has to re-evaluate a shifting reference on every keystroke.
Converting the source range into an Excel Table (Insert > Table) before referencing it in VLOOKUP tends to help, since Table references automatically resize with the data and stay consistent without needing manual $ locking.
Before applying a new VLOOKUP formula to an entire column, it's worth testing it on a single row and manually verifying the result against the source data, since a mistake caught in one cell takes seconds to fix, while the same mistake copied down five hundred rows takes much longer to untangle.
This is especially worth doing whenever the source table_array has changed shape recently, since a resized or reordered table is the single most common reason a previously reliable formula starts returning unexpected results.
What's the core difference between VLOOKUP and HLOOKUP?
VLOOKUP searches down a column for a match; HLOOKUP searches across a row for a match.
How do I know which one my spreadsheet needs?
Check whether your categories run down the left column (VLOOKUP) or across the top row (HLOOKUP).
What does row_index_num mean in HLOOKUP?
It counts rows downward from the top of the selected range, similar to how col_index_num counts columns in VLOOKUP.
Is HLOOKUP as commonly used as VLOOKUP?
No, most spreadsheets are organized with data running down rows, so VLOOKUP comes up far more often in practice.
Can the same table use both VLOOKUP and HLOOKUP?
Yes, if a table has both row and column headers, either formula can search along the relevant axis.
Does XLOOKUP replace the need to choose between them?
Largely yes, since XLOOKUP can search either direction by selecting the appropriate lookup_array and return_array.
Can trailing spaces in a cell cause a lookup to fail?
Yes, an extra space at the end of a value is invisible but prevents an exact match; TRIM() around the lookup value often fixes this.
Does the order of rows in the source data matter for an exact match?
No, an exact match search works regardless of row order; only an approximate match requires sorted data.
Is it safe to have duplicate values in the lookup column?
A lookup formula will only return the first matching row it finds, so duplicates can silently return the wrong record if not accounted for.
Should I test a new formula on one row before applying it everywhere?
Yes, verifying a single result against the source data first catches setup mistakes before they're copied across an entire column.
Ready to stop fighting #N/A errors and match your spreadsheets visually?