VLOOKUP and HLOOKUP in Excel: Differences and Examples

Formulas & functions · Updated November 18, 2026

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 it. In Microsoft Excel for Microsoft 365 on Windows, use either function with an exact-match option when you need to find a specific text or number.

What VLOOKUP and HLOOKUP are for

Both functions look for a value in one part of a table, then return a related value from the same row or column. They are useful when a worksheet stores records in a grid and you want a formula to retrieve a corresponding item, price, label, or measurement.

The letter V means vertical: VLOOKUP looks down a column. The letter H means horizontal: HLOOKUP looks across a row. Their shared purpose is lookup, but their search direction differs. Neither function automatically searches every cell in a range; each is told which column or row contains the lookup values.

For example, a product list might place product IDs in its first column and prices in a later column. VLOOKUP can find an ID and return its price. A report with months across its top row can use HLOOKUP to find a month and return a value from a row underneath.

How the functions work

Both functions take four arguments: the value to find, the table range, the position of the return column or row within that range, and whether the match should be exact or approximate. The position number is counted inside the selected range, not from the worksheet’s column or row labels.

  • Lookup value: The value Excel searches for, such as an ID in a cell.
  • Table array: The complete range containing both the lookup values and the return values.
  • Column or row index: For VLOOKUP, the number of the return column in the selected range; for HLOOKUP, the number of the return row.
  • Match option: FALSE requests an exact match. TRUE requests an approximate match. If this argument is omitted, Excel uses approximate matching.

For VLOOKUP, the first column of the selected range must contain the values to search. For HLOOKUP, those values must be in the first row. The functions then return a value from the same record: across the row for VLOOKUP, or down the column for HLOOKUP.

VLOOKUP example with an exact match

Suppose cells A2:C5 contain a small product table: column A has product IDs, column B has product names, and column C has prices. Cell E2 contains P-105, whose row has a price of 8. With the lookup value in E2 and the table in A2:C5, enter this formula:

=VLOOKUP(E2,A2:C5,3,FALSE)

The result is 8. The 3 tells Excel to return the third column of A2:C5, which is column C. FALSE tells it to find the ID exactly, rather than choosing a nearby value. The first column of the range is A, so the lookup works because the IDs are in that column.

To return the product name from the same table instead, change the column index to 2. The range still starts at A2, so column B is the second column within that range. A frequent source of mistakes is counting the worksheet’s column letters instead of counting from the left edge of the table array.

HLOOKUP example with an exact match

Suppose A1:D3 contains a monthly report. The first row has Jan, Feb, Mar, and Apr in cells A1:D1; the second row has units sold, with 42, 38, 51, and 47 respectively. Cell G1 contains Mar. With the month names in the first row of the table and units in its second row, use:

=HLOOKUP(G1,A1:D3,2,FALSE)

The result is 51. Excel searches across the first row of A1:D3 for Mar, then returns the value in the second row of that range in the matching column. The row index 2 is counted from the top of the selected range, not from the worksheet’s row numbers.

If the value you want is on the third row of the selected range, use 3 as the row index. HLOOKUP can return a value only from a row included in its table array, just as VLOOKUP cannot return from a column outside its selected range.

Choosing exact or approximate matching

For most everyday lookups—finding an employee ID, product code, or named category—use FALSE for VLOOKUP or HLOOKUP. Exact matching avoids returning a result for a different value just because it is numerically close. The lookup value and the source value should also be the same kind of data: a number stored as text may not match the equivalent numeric value as expected.

Approximate matching is useful when the table defines ranges, such as score bands. In that case, the first lookup column or row must be sorted in ascending order. Excel finds the largest value that is less than or equal to the lookup value. If the values are not sorted correctly, the result may be misleading rather than an obvious error.

For example, with minimum scores 0, 60, 70, 80, and 90 in A2:A6 and their corresponding grade labels in B2:B6, a score of 83 should match the threshold 80 and return the grade on that row. With that table in A2:B6 and the score in D2, use:

=VLOOKUP(D2,A2:B6,2,TRUE)

The result is the label beside 80, because 80 is the largest listed threshold not greater than 83. The same approximate-match principle applies to HLOOKUP, using sorted threshold values across its first row. If you are unsure whether approximate matching is appropriate, choose FALSE.

Limitations and common problems

VLOOKUP can return values only to the right of its search column, while HLOOKUP can return values only below its search row. They also return the first match they encounter. If an ID appears more than once, a formula will not automatically report every matching record; it returns the value associated with the first occurrence.

Common errors and their usual causes include:

  • #N/A: No exact match was found, or the lookup value differs from the table value. Check spelling, extra spaces, and whether numbers are stored as text.
  • #REF!: The requested column or row index is larger than the selected table range. Expand the range or correct the index.
  • #VALUE!: An index argument is invalid, such as a value below 1.
  • A plausible but incorrect result: Approximate matching may be in use when an exact match was intended, or the lookup range may not be sorted for an approximate lookup.

Also check that the lookup field is the first column or row of the selected range. If a formula is copied to other cells and the table should stay fixed, use absolute references such as $A$2:$C$5 for the table array. A dollar sign in a cell reference keeps that part of the reference from shifting as the formula is copied.

When a formula is correct but a result seems unexpected, inspect the source cells rather than only the formula. Look for leading or trailing spaces, inconsistent punctuation, duplicate keys, and numeric values saved as text. The TRIM function can remove ordinary extra spaces from text, but it does not resolve every difference in data type or formatting.

VLOOKUP, HLOOKUP, and newer alternatives

Use VLOOKUP when records are arranged vertically and the key is in the leftmost column of the relevant range. Use HLOOKUP when the keys are arranged horizontally in the top row. The choice is about the layout of the lookup table, not about which function is generally more powerful.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can search in one range and return a corresponding value from another, including values to the left of the lookup range. It also uses exact matching by default. This makes it a convenient option when a workbook will be opened in a version of Excel that supports it. Older Excel versions may not recognize XLOOKUP, so VLOOKUP or HLOOKUP can be useful for compatibility.

INDEX and MATCH are another established combination, including for older Excel versions. They can separate the lookup range from the return range and can retrieve values to the left or above the search area, but their formulas can be less straightforward for beginners. Whichever method you choose, verify that the lookup key is unique if you expect one particular result.

Practical habits for reliable lookup formulas

  • Use FALSE for codes and labels that need to match exactly.
  • Keep lookup keys in a consistent format; avoid mixing numeric IDs with IDs stored as text.
  • Include the full return range in the table array and count its columns or rows from the range’s first edge.
  • Use absolute references for a fixed table when copying a formula down or across.
  • Test a known lookup manually and confirm the result against the source row or column.
  • Consider XLOOKUP when workbook users have a supported Excel version and the table layout makes VLOOKUP or HLOOKUP awkward.

In Excel for Microsoft 365 on Windows, you can type a formula directly into a cell and press Enter. Excel for the web supports these lookup functions too. Excel for Mac also includes them, although ribbon layout and keyboard shortcuts can differ from Windows. The formula syntax and the meaning of the arguments remain the same.

Frequently Asked Questions

Is VLOOKUP the same as HLOOKUP?

No. They use similar arguments, but VLOOKUP searches vertically in the first column of a range and returns from a column to the right. HLOOKUP searches horizontally in the first row and returns from a row below.

Why does VLOOKUP return #N/A?

Usually, the lookup value is not an exact match in the first column of the selected range. Check for extra spaces, different data types, spelling differences, or a lookup range that starts in the wrong column.

What does FALSE mean in VLOOKUP?

FALSE tells VLOOKUP to require an exact match. It is generally the safer option for IDs, product codes, and labels. HLOOKUP uses FALSE in the same way.

Can VLOOKUP search to the left?

No. The lookup values must be in the first column of the selected range, and VLOOKUP returns from a column to the right. XLOOKUP or INDEX with MATCH can handle a lookup that needs to return a value from the left.

Which is better, VLOOKUP or XLOOKUP?

For supported Excel versions, XLOOKUP is more flexible and defaults to exact matching. VLOOKUP remains useful in older workbooks and versions. Choose based on the workbook’s layout and the Excel versions its users need to support.

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.