VLOOKUP and HLOOKUP in Excel: Formulas and Uses

Formulas & functions · Updated November 18, 2026

In Excel, VLOOKUP searches down the first column of a table and returns a value from a column to its right; HLOOKUP searches across the first row and returns a value from a row below. For an exact match, use =VLOOKUP(E2,$A$2:$C$5,3,FALSE) or =HLOOKUP("Q3",$B$1:$E$3,3,FALSE), adjusting the lookup value, range, and return-column or return-row number to fit your data.

What VLOOKUP and HLOOKUP do

Both functions look for a value in a selected table and return a related value from that same table. Their main difference is the direction of the lookup. The “V” in VLOOKUP means vertical: the lookup values are arranged in a column. The “H” in HLOOKUP means horizontal: the lookup values are arranged in a row.

These functions are useful when you have a reference list, such as product codes and prices, employee IDs and departments, or quarterly figures. You give Excel a value to find, the range where it should search, and the position of the result you want. Excel returns the value at the intersection of the matching row or column and the requested return position.

The lookup value must be in the first column of the VLOOKUP range or the first row of the HLOOKUP range. VLOOKUP cannot return a value to the left of its lookup column, and HLOOKUP cannot return a value above its lookup row. This constraint is important when arranging a table or deciding whether to use a different function.

VLOOKUP formula and example

Suppose product IDs are in A2:A5, product names are in B2:B5, and prices are in C2:C5. Cell E2 contains the product ID P-207, and the table has these rows: P-104, Cable, 8.50; P-207, Mouse, 24.00; P-315, Keyboard, 39.00; and P-420, Hub, 31.50. To return the price, enter =VLOOKUP(E2,$A$2:$C$5,3,FALSE) in another cell. It returns 24.00.

The arguments have specific jobs:

  • E2 is the lookup value: the product ID Excel should find.
  • $A$2:$C$5 is the table array. Its first column contains the IDs. Dollar signs make the range absolute, so it stays fixed if you copy the formula to another cell.
  • 3 is the column index number. It asks for the third column within the selected range, which is column C in this example.
  • FALSE requests an exact match. This is generally the right choice for IDs, names, and other distinct labels.

The index counts columns from the left edge of the selected range, not from the worksheet’s column A. If the range began at B2 instead, its first column would be column index 1. A range that begins in the wrong place can therefore produce an error or return a different field than intended.

HLOOKUP formula and example

HLOOKUP follows the same general pattern, but the lookup values run across a row. Suppose B1:E1 contains Q1, Q2, Q3, and Q4; B2:E2 contains unit totals; and B3:E3 contains revenue of 12,500, 14,200, 13,800, and 16,100. With those headings and values in B1:E3, =HLOOKUP("Q3",$B$1:$E$3,3,FALSE) returns 13,800.

Here, “Q3” is the value Excel searches for in the top row of the range. The number 3 tells Excel to return a value from the third row of that range, which is worksheet row 3. FALSE again means that the heading must match exactly. Like VLOOKUP, the row index is counted from the top of the selected range, not from the worksheet’s row numbers.

HLOOKUP can be convenient for compact reports where time periods or categories are laid out horizontally. However, many working datasets put one record on each row and field names in a header row. In that common arrangement, VLOOKUP or a newer lookup function is usually more suitable.

Exact match versus approximate match

The final argument controls how Excel matches the lookup value. FALSE means exact match; TRUE means approximate match. You can use 0 for FALSE and 1 for TRUE, but spelling out FALSE or TRUE can make a formula easier to read. If you omit the final argument, both VLOOKUP and HLOOKUP use approximate matching by default. That default is a frequent source of mistakes: for identifiers and names, specify FALSE rather than relying on omission.

Approximate matching is useful for ordered thresholds, such as assigning a grade from a score or a shipping band from a package weight. For example, with grade thresholds in A2:A6 and their labels in B2:B6—0/F, 60/D, 70/C, 80/B, and 90/A—and a score of 84 in E2, =VLOOKUP(E2,$A$2:$B$6,2,TRUE) returns B. Excel finds the largest threshold that is less than or equal to 84, which is 80.

For approximate matching to work predictably, the first column of a VLOOKUP table or the first row of an HLOOKUP table must be sorted in ascending order. If that lookup range is not sorted, Excel may return an unexpected result rather than an obvious error. Exact-match lookups do not require the lookup list to be sorted.

How to choose the right lookup

Choose between VLOOKUP and HLOOKUP based on how the lookup field is arranged, not on which function sounds more familiar. Ask where Excel should search and where the answer sits relative to that location.

  • Use VLOOKUP when the key is in the first column of a vertical table and the answer is in a column to its right.
  • Use HLOOKUP when the key is in the top row of a horizontal table and the answer is in a row below it.
  • Use an exact match for product codes, employee numbers, account labels, and most other identifiers.
  • Use an approximate match for sorted numeric bands or thresholds, and verify the sort order before relying on the result.

For a new dataset, consider whether it can be arranged with one record per row and one field per column. A vertical table is often easier to filter, sort, expand, and analyze than a wide layout with many categories spread across columns. HLOOKUP still has a role in horizontal summary tables, but it is not necessary to preserve a horizontal layout if the data is easier to maintain in rows.

Common errors and how to fix them

#N/A usually means Excel could not find the requested value using the chosen match type. Check for spelling differences, extra spaces, or a lookup value that is outside the selected range. Also check whether one value is stored as text while the other is stored as a number; a cell that displays 123 can be text in one place and numeric in another.

#REF! can occur when the requested column or row index is larger than the number of columns or rows in the table array. For instance, if a VLOOKUP range has three columns, a column index of 4 points outside the range. Count the selected range from its first column or row, rather than using worksheet coordinates.

A formula can return a plausible but incorrect result if the range starts in the wrong place, the index points to the wrong field, or approximate matching is being used when an exact match was intended. If copying a formula down causes the table range to move, lock it with dollar signs, as in the examples above. If the lookup table changes shape often, review the formula after adding or removing columns because a numeric index may then refer to a different field.

When a formula appears as text instead of calculating, check whether the cell is formatted as Text or whether an apostrophe was entered before the equals sign. Change the cell format to General, then re-enter the formula. In some regional settings, Excel uses semicolons instead of commas between formula arguments. The examples here use commas, as requested for standard English-language Excel syntax; the separator accepted by a particular installation depends on its regional settings.

Alternatives and newer lookup options

XLOOKUP is a more flexible option in Microsoft 365 and Excel 2021 or later. It can search in one range and return a result from a separate range, so the return values do not have to be to the right of the lookup values. With product IDs in A2:A5, prices in C2:C5, and the requested ID in E2, =XLOOKUP(E2,A2:A5,C2:C5,"Not found") returns the corresponding price or the text Not found if there is no match. In the product example above, it returns 24.00 for P-207.

Another option is INDEX with MATCH, which can work in older Excel versions and can look in either direction. It takes more explanation than a simple lookup, but separates the result range from the search range. When choosing, consider the version of Excel used by everyone who needs to open the workbook: a newer function may not calculate in an older release.

For Excel for Microsoft 365 on Windows, formulas can be entered directly in a cell and confirmed with Enter. The same basic VLOOKUP and HLOOKUP formulas work in Excel for Mac and Excel for the web, though interface details and regional argument separators can differ. XLOOKUP availability depends on the Excel version, so check compatibility before sharing a workbook with users on older installations.

Frequently Asked Questions

What is the basic VLOOKUP formula in Excel?
VLOOKUP takes a value to find, a table range, a return-column number, and an optional match setting. For most identifiers, choose an exact match with FALSE. The lookup field needs to be in the first column of the selected range.

What is the basic HLOOKUP formula in Excel?
HLOOKUP takes a value to find, a table range, a return-row number, and an optional match setting. It searches the first row of the range, then returns a value from the specified row beneath it.

Why does VLOOKUP return the wrong value?
Check the column index, the first column of the range, and the final match argument. An omitted match argument defaults to approximate matching, which can be unsuitable for IDs. Also confirm that the lookup data is consistent and, for approximate matches, sorted ascending.

Can VLOOKUP search to the left?
No. VLOOKUP returns data from a column to the right of the first column in its table array. Use XLOOKUP in supported Excel versions, or a suitable INDEX and MATCH combination, when the result is to the left.

Should I use VLOOKUP or XLOOKUP?
Use XLOOKUP when the workbook’s Excel versions support it and you want separate lookup and return ranges or a built-in not-found result. VLOOKUP remains useful for compatibility with older versions and for straightforward tables where the answer is to the right of the lookup column.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.