Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 29 ยท Lookups and Dates

VLOOKUP, HLOOKUP and Their Limits

Learn VLOOKUP and HLOOKUP in Google Sheets: exact versus approximate match, the column index trap, the left-lookup limit, with Python stand-ins.

A lookup finds a row by a key and returns another value from that row: the price of a product code, the department of an employee ID, the tax rate for an income bracket. It replaces endless copy and paste between tables, and it is one of the skills employers ask about most.

VLOOKUP (vertical lookup) is the classic tool and HLOOKUP its sideways twin. They are everywhere in existing spreadsheets, so you need to read them fluently, and you need to know their limits so you can reach for XLOOKUP, INDEX and MATCH when those limits bite.

VLOOKUP in four arguments

Search key The value you look for.
Range The table. The key must be in its first column.
Index Which column to return, counting from 1.
Is sorted FALSE for exact match. TRUE for approximate.
1 / 2

How VLOOKUP works

The syntax is VLOOKUP(search_key, range, index, [is_sorted]). Say a product table lives in A2:C6: code in column A, name in B, price in C. To get the price for the code typed in F2:

TEXT
=VLOOKUP(F2, A2:C6, 3, FALSE)

Sheets scans the first column of the range (A2:A6) from the top for F2. At the first match it returns the value from the 3rd column of that range (C). Counting is relative to the range, not the sheet: the range starts at column A, so index 3 means column C. If you used B2:D6 instead, index 3 would mean column D.

If nothing matches, you get #N/A (see Understanding Formula Errors). Text matches ignore capitalization, and with FALSE the key must match the whole cell.

Google Sheets itself cannot run on this page, so the formulas are shown read-only. The TryIt blocks are Python stand-ins that follow the same rules, so you can see what happens inside a lookup.

Python Stand-in: exact-match VLOOKUP

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Exact versus approximate match

The fourth argument decides how Sheets matches, and it is the most misunderstood part of the function.

  • FALSE (or 0): exact match. Returns the first row whose key equals the search key, otherwise #N/A. Use this for IDs, codes and names. The data does not need to be sorted.
  • TRUE (or 1) or omitted: approximate match. Returns the row with the largest key that is less than or equal to the search key. The first column must be sorted ascending, or the result can be wrong without any error.

Approximate match is exactly what you want for banded tables: tax brackets, shipping tiers, grade boundaries. A table with 0 โ†’ F, 60 โ†’ D, 70 โ†’ C, 80 โ†’ B, 90 โ†’ A returns B for 85, because 80 is the largest boundary that does not exceed 85.

The trap is that the argument is optional and defaults to approximate. Someone writes =VLOOKUP(F2, A2:C6, 3), tests it on one row where it happens to work, and later it returns wrong prices for other codes. Always type the last argument explicitly.

Python Stand-in: approximate match for grade bands

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If the first column is not sorted, the approximate search does not scan row by row; it jumps around the list, so it can land on a wrong row and return a plausible-looking wrong value. Sort the key column, or switch to FALSE.

HLOOKUP: the sideways version

HLOOKUP(search_key, range, index, [is_sorted]) is identical except that the keys are in the first row and the index counts rows. It suits tables laid out horizontally, such as months across the top and a row of targets beneath.

TEXT
=HLOOKUP("Mar", B1:M3, 2, FALSE)

This searches B1:M1 for "Mar" and returns the value from the 2nd row of the range. Same rules, same traps. Most data is arranged in columns, so VLOOKUP is far more common.

Python Stand-in: HLOOKUP is VLOOKUP turned sideways

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The limits of VLOOKUP

These weaknesses explain why newer functions exist.

1. The key must be in the first column. VLOOKUP only looks right. If the ID is in column C and the name you want is in column A, VLOOKUP cannot return it. Workarounds: rearrange columns (often impossible), build a helper column, or use INDEX and MATCH or XLOOKUP.

2. The column index is a hard-coded number. Index 3 means "the third column of the range" forever. If a teammate inserts a column in the middle, Sheets widens the range, but the formula still returns the third column, which is now the wrong one. Counting columns by eye also invites mistakes in wide tables. A common fix is MATCH("Price", A1:E1, 0) in place of the literal.

3. It returns only the first match. If a code appears twice, you never see the second row. Count duplicates first, or aggregate with SUMIF (see SUMIF, COUNTIF, AVERAGEIF and Friends).

4. One column at a time. To fetch the name and the price you need two formulas. (An array of indexes, {2, 3}, can work with ARRAYFORMULA, but it is awkward.)

5. Invisible mismatches. Trailing spaces, numbers stored as text, or different formatting of the same ID make a perfect-looking key return #N/A. TRIM the keys, and compare with =ISNUMBER(A2) if you suspect text numbers.

Python Stand-in: the left-lookup problem and the index shift

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Practical advice

  • Always write the last argument: FALSE for exact lookups, TRUE only for sorted bands.
  • Keep lookup tables on their own sheet with clear headers, and use whole-column ranges such as Prices!A:C so new rows are included.
  • Wrap lookups that may fail in IFNA(..., "Not found") from IF, AND, OR and IFS rather than IFERROR, which would also hide a mistyped range.
  • Use absolute references ($A$2:$C$100) for the table so it does not slide when you copy the formula down; see Relative, Absolute and Mixed References.
  • In new work prefer XLOOKUP, covered in the next lesson. It looks in any direction, takes a not-found message, and does not use a column number.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does VLOOKUP do if you leave out the fourth argument?

    The fourth argument defaults to TRUE, which means approximate match. That is why you should always type FALSE for exact lookups.
  2. 2. A tax table has boundaries 0, 10000, 40000 in column A. For which kind of lookup is approximate match ideal?

    Approximate match returns the largest boundary that does not exceed the key, which is how banded tables work.
  3. 3. Your formula is =VLOOKUP(A2, C:E, 2, FALSE). Which column does it return?

    The index counts within the range, which starts at C. Index 1 is C, index 2 is D.
  4. 4. Which of these is a real limitation of VLOOKUP?

    VLOOKUP searches the first column of the range and returns from columns to its right. XLOOKUP and INDEX/MATCH remove that restriction.

Summary

  • VLOOKUP(key, range, index, FALSE) finds the key in the first column and returns the value from the numbered column of the range.
  • The last argument defaults to approximate match; write FALSE for exact lookups and use TRUE only with a sorted key column.
  • HLOOKUP does the same with keys in the first row and an index that counts rows.
  • Limits: left-to-right only, a fragile hard-coded index, first match only, and sensitivity to spaces and text numbers.
  • Guard with IFNA and absolute references, and prefer XLOOKUP for new work.

Next: XLOOKUP, INDEX and MATCH