Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
VLOOKUP vs HLOOKUP: vertical vs horizontal lookups
Topic:VLOOKUPExcel & 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
- 1Identify data orientation: if lookup values are in a column (vertical), use VLOOKUP; if they are in a row (horizontal), use HLOOKUP.
- 2VLOOKUP syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) — set range_lookup to FALSE for an exact match.
- 3HLOOKUP syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) — set range_lookup to FALSE for an exact match.
- 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
- VLOOKUP can only look to the right — if your return column is to the left of the lookup column, use INDEX/MATCH or XLOOKUP instead.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
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-toHow 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 fixFix #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-toHow 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 fixFix 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-toHow 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.