FormulaCraft

VLOOKUP vs HLOOKUP: vertical vs horizontal lookups

Topic:VLOOKUP
Excel & Google Sheets
=VLOOKUP(A2,B2:C6,2,FALSE)

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

Step by step

  1. 1Identify data orientation: if lookup values are in a column (vertical), use VLOOKUP; if they are in a row (horizontal), use HLOOKUP.
  2. 2VLOOKUP syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) — set range_lookup to FALSE for an exact match.
  3. 3HLOOKUP syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) — set range_lookup to FALSE for an exact match.
  4. 4In Excel 365 or Google Sheets, consider XLOOKUP as a replacement: =XLOOKUP(A2, B2:B6, C2:C6) works in either direction and does not require you to count column or row numbers.

Tips

Need it for your exact data?

Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.

Frequently asked

Why does VLOOKUP return the wrong result?

The most common causes are: range_lookup left as TRUE (approximate match) when you need FALSE (exact match), the lookup column not being the leftmost column of the table, or a mismatch between text and numeric lookup values.

Should I switch to XLOOKUP?

Yes, if your Excel version supports it (Excel 365 or 2021). XLOOKUP handles left-side lookups, returns multiple columns, and manages #N/A more cleanly than VLOOKUP.

More on VLOOKUP

See all →
Reference

VLOOKUP

Look up a value in the first column of a range and return a value from another column in the same row.

How-to

How to VLOOKUP from another sheet

Pull a value from a different tab with VLOOKUP by prefixing the range with the sheet name. Works in Excel and Google Sheets.

Error fix

Fix #N/A

Fix #N/A errors in VLOOKUP in Excel and Google Sheets — covers missing matches, extra spaces, data-type mismatches, and range mistakes.

How-to

How to VLOOKUP with multiple criteria

Look up a value that matches two or more conditions using INDEX/MATCH with joined keys. Works in Excel 365 and Google Sheets.

Error fix

Fix Wrong value

VLOOKUP returns an incorrect value in Excel and Google Sheets — fix wrong column index, approximate match mode, unsorted data, and duplicate lookup keys.

How-to

How to fix VLOOKUP returning #N/A

VLOOKUP shows #N/A when it can’t find a match. Here are the real causes — exact-match, spaces, text-vs-number — and how to fix each.

Formulas used

VLOOKUPHLOOKUPINDEX

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: