VLOOKUP vs HLOOKUP: What's the Difference?
By ExcelReady TeamUpdated 7 min read
The short answer
VLOOKUP searches down the first column of a table and returns a value from a column to the right. HLOOKUP searches across the top row and returns a value from a row below. Use VLOOKUP when your records run down the sheet, which is most tables, and HLOOKUP only when the labels run across the top.
Key takeaways
- V stands for vertical: VLOOKUP searches the first column and counts columns to the right.
- H stands for horizontal: HLOOKUP searches the top row and counts rows down.
- Both default to approximate match, so add FALSE as the fourth argument for exact matches.
- Neither can look left or up. XLOOKUP and INDEX MATCH can, in either direction.
- Most real tables run down the sheet, so you'll write VLOOKUP far more often than HLOOKUP.
On this page+
VLOOKUP vs HLOOKUP is only a question of direction. VLOOKUP (vertical lookup) searches down the first column of a table and returns a value from a column to the right; HLOOKUP (horizontal lookup) searches across the top row and returns a value from a row below. Everything else, from the FALSE at the end to the #N/A when nothing matches, works the same way. This comparison is for anyone who knows VLOOKUP and wants to know when the sideways version is the right tool.
VLOOKUP vs HLOOKUP at a glance#
The two functions are mirror images: one counts columns, the other counts rows.
| Feature | VLOOKUP | HLOOKUP |
|---|---|---|
| Stands for | Vertical lookup | Horizontal lookup |
| Searches | The first column of the table | The first row of the table |
| Returns from | A column to the right | A row below |
| Third argument | col_index_num (column number) | row_index_num (row number) |
| Default match | Approximate (add FALSE for exact) | Approximate (add FALSE for exact) |
| Look left or up | No | No |
| Typical table | Records listed down the sheet | Labels across the top, like months or zones |
| Excel versions | All versions | All versions |
How the syntax differs#
Both functions take the same four arguments; only the third one changes meaning.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])lookup_value: what to find, such as a code or a name.table_array: the table. VLOOKUP searches its first column; HLOOKUP searches its first row.col_index_num/row_index_num: which column (VLOOKUP) or row (HLOOKUP) to bring back, counted from 1 at the edge of the table, not the edge of the sheet.range_lookup:FALSEfor an exact match.TRUE, or leaving it out, means approximate match, which needs the search column or row sorted smallest to largest.
The VLOOKUP function guide walks through each argument, and our beginner guides to VLOOKUP step by step and HLOOKUP and when to use it cover each function on its own.
The same lookup, both ways#
The clearest comparison is one rate card laid out in two directions. The task: find the per-kg shipping rate for zone C.
A vertical table: use VLOOKUP#
Here each zone is a row:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Zone | Base fee | Per kg | Days |
| 2 | A | 5.00 | 1.20 | 2 |
| 3 | B | 6.50 | 1.50 | 3 |
| 4 | C | 8.00 | 1.80 | 4 |
| 5 | D | 10.00 | 2.10 | 5 |
=VLOOKUP("C",A2:D5,3,FALSE)VLOOKUP goes down column A, finds C in row 4, then moves to the 3rd column of the table (column C, Per kg). The result is 1.80.
A horizontal table: use HLOOKUP#
Here the same data is turned on its side, so each zone is a column:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Zone | A | B | C | D |
| 2 | Base fee | 5.00 | 6.50 | 8.00 | 10.00 |
| 3 | Per kg | 1.20 | 1.50 | 1.80 | 2.10 |
| 4 | Days | 2 | 3 | 4 | 5 |
=HLOOKUP("C",B1:E4,3,FALSE)HLOOKUP goes across row 1, finds C in column D, then moves down to the 3rd row of the table (row 3, Per kg). The result is again 1.80. Notice the table starts at B1, so the labels in column A are not part of it; if you used A1:E4, the row number would still be 3 because rows are counted from the top.
The modern alternative: XLOOKUP#
In Excel 2021, Excel 2024 and Microsoft 365, XLOOKUP handles both layouts with the same shape:
=XLOOKUP("C",A2:A5,C2:C5)
=XLOOKUP("C",B1:E1,B3:E3)The first searches down a column, the second across a row. Both return 1.80, and neither needs a row or column number. Our comparison of VLOOKUP vs XLOOKUP covers the other differences.
When to use VLOOKUP#
Use VLOOKUP whenever your records run down the sheet, one record per row. That covers most business data:
- Staff lists, price lists and product catalogues, where each row is one person or item.
- Data exported from other systems, which almost always arrives with headers on top and records below.
- Job tests, where VLOOKUP is the lookup asked for most often. Our guide on how to pass an Excel test for a job interview lists the other common tasks.
When to use HLOOKUP#
Use HLOOKUP when the thing you search for is a column header and the details sit in rows below it. Typical cases:
- Rate cards and price grids with zones, sizes or plans across the top.
- Monthly or yearly reports with months or years as column headers, such as finding the March figure in a budget row.
- Older workbooks that already use HLOOKUP, where switching style would confuse colleagues.
If a horizontal table is something you control, consider whether it should run down the sheet instead. Records in rows are easier to filter, sort and summarise with pivot tables.
Common mistakes with VLOOKUP and HLOOKUP#
The two functions share the same pitfalls, plus one that is easy to make when switching between them:
- Mixing up the third argument. In HLOOKUP it's a row number. Typing the column number you'd use in VLOOKUP returns the wrong value or #REF!.
- Leaving out FALSE. Both default to approximate match and can return a wrong result on unsorted data, with no error to warn you.
- Counting from the sheet instead of the table. Row 3 of
B1:E4is sheet row 3, but row 2 ofB2:E4is also sheet row 3. - Searching the wrong edge. VLOOKUP only searches the first column and HLOOKUP only the first row. Neither can look left or up.
- Not locking the table before filling. Use
$B$1:$F$4(press F4, or ⌘T on a Mac) so the table doesn't slide as you fill down with Ctrl+D (⌘D). - Text vs number mismatches. A zone code typed as the number 1 won't match the text "1". Both show #N/A.
How to stop counting rows and columns#
The weak spot of both functions is the hard-coded number in the third argument. Insert a new row into the rate card and HLOOKUP's row 3 may now point at the wrong line, with no error to warn you. You can let MATCH find the number instead:
=VLOOKUP("C",A2:D5,MATCH("Per kg",A1:D1,0),FALSE)
=HLOOKUP("C",B1:E4,MATCH("Per kg",A1:A4,0),FALSE)In the vertical table, MATCH finds "Per kg" in the header row A1:D1 at position 3. In the horizontal table, it finds "Per kg" in the label column A1:A4, also at position 3. Both formulas return 1.80, and they keep working if rows or columns move, as long as the labels stay the same.
Can HLOOKUP and VLOOKUP be combined?#
Yes, but a two-way lookup is easier with INDEX and MATCH, or with XLOOKUP nested inside XLOOKUP. Our guide to two-way lookups in Excel shows both. If you only need to change a table's direction, copy it and use Paste Special > Transpose, or the TRANSPOSE function, covered in TRANSPOSE in Excel.
Other comparisons in this series use the same side-by-side approach, for example MAX vs LARGE for finding top values and DAYS vs DATEDIF for date gaps.
Our recommendation#
Learn VLOOKUP first and well, then treat HLOOKUP as the same skill turned sideways. Use VLOOKUP for any table with records in rows, and HLOOKUP only when the search values are column headers. If everyone has Excel 2021, Excel 2024 or Microsoft 365, XLOOKUP covers both directions with one function and is the better default for new files.
In short: VLOOKUP for down, HLOOKUP for across, XLOOKUP for either.
Practise it#
Try the HLOOKUP exercise on this page on a sideways rate card. Then practise VLOOKUP exact match on a product table, and work through the HLOOKUP lesson to look up base fees, per-kg rates and delivery days, lock the table and combine two HLOOKUPs in one shipping-cost formula.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The rate card runs sideways: zones are across the top row. In B7, use HLOOKUP to find the per-kg rate for the zone in B6. Search B1:E4. Per kg is row 3 of that table. End with FALSE for an exact match.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | D | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | C | |||||
| 7 | Per kg | |||||
| 8 | ||||||
| 9 | ||||||
| 10 |
Tip: HLOOKUP takes what to find (B6), the table (B1:E4), the row number counted from the top of the table (3), and FALSE.
Practise this in a lesson
Frequently asked questions
What is the difference between VLOOKUP and HLOOKUP?
The direction. VLOOKUP looks for a value in the first column of a table and returns a value from the same row. HLOOKUP looks for a value in the first row and returns a value from the same column.
When should I use HLOOKUP instead of VLOOKUP?
Use HLOOKUP when the values you search for are laid out across the top row, such as months, zones or years as column headers, and the details sit in the rows below.
Is HLOOKUP still used?
Yes, mainly in older workbooks and job tests that check you know both directions. In Excel 2021, Excel 2024 and Microsoft 365, XLOOKUP handles horizontal tables too, so new files often use it instead.
Can HLOOKUP look up a value in a row below the top row?
No. HLOOKUP always searches the first row of the table you give it. To search a different row, start the table at that row, or use INDEX with MATCH or XLOOKUP.
Do VLOOKUP and HLOOKUP need sorted data?
Only for approximate match (TRUE or a missing fourth argument), where the search column or row must be sorted smallest to largest. With FALSE for an exact match, the data can be in any order.