Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 93 ยท Lookup and Reference

VLOOKUP and Its Limits

Learn how Excel's VLOOKUP works, why exact match matters, what causes #N/A, and the limits that make INDEX/MATCH and XLOOKUP better choices.

A lookup finds a value in a table by a key. You have a product code and want its price; you have an employee ID and want the department. VLOOKUP (the "V" is for vertical) has been Excel's standard answer for decades, and you will meet it in almost every older workbook you open.

It is simple to learn and easy to get subtly wrong. This lesson teaches the syntax, the single argument that most mistakes come from, the common causes of #N/A, and the design limits that led Microsoft to add newer functions. Excel itself cannot run here, so the formulas are shown as real Excel formulas and the live examples run them on a small stand-in engine in JavaScript (a pandas version at the end shows the same job in Python). The stand-in behaves like Excel for these functions, but it is not Excel's code.

VLOOKUP in four arguments

Lookup value What to find.
Table array Where to look.
Column index Which column to return.
Range lookup FALSE for exact match.

The first column of the table is the one that is searched.

1 / 2

The syntax

TEXT
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: the key to find, usually a cell reference such as E2.
  • table_array: the block of cells to search. Excel looks for the key in the first column of this block only.
  • col_index_num: which column of the block to return, counted from 1 inside the block (not the sheet column).
  • range_lookup: FALSE (or 0) for an exact match; TRUE (or 1, or leaving it out) for an approximate match.

For a table in A2:C6 where column A holds IDs, B names and C prices:

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

reads: "find the ID in E2 in column A and give me the value from the third column of the block", which is the price. Anchor the table with $ when you copy the formula down ($A$2:$C$6), as described in Cell References: Relative, Absolute, Mixed.

Always type FALSE (unless you mean approximate)

The fourth argument is optional, and the default is TRUE, which means approximate match. In that mode Excel assumes the first column is sorted ascending and uses a fast binary search to find the largest key that is less than or equal to your value. That is perfect for tax brackets and grade bands (the next lesson, HLOOKUP and Approximate-Match Lookups, covers them), but on an ordinary unsorted list it can return a wrong row without any error.

The example below builds a small price list and runs ten lookups. Read the output next to the formulas in the code. Pay special attention to the lookup of "Lamp" with the fourth argument left out.

JS Exact match, approximate match and the common #N/A causes

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

No output.

What the lines show:

  • ID 103, exact works: the price 40.
  • ID 999 is not in the table, so you get #N/A, "not available". This is the correct result. Handle it deliberately with IFNA (see Handling Errors and Blanks in Formulas).
  • "103" typed as text fails, because the number 103 and the text "103" are different things to a lookup.
  • "Bag " with a trailing space fails, because the key does not equal Bag exactly. TRIM removes the stray space.
  • "Lamp" without FALSE returned the price of a different product here. The names are not sorted, so the binary search wandered off. Real Excel uses the same idea, and with unsorted data the result is unpredictable; the dangerous part is that you get no warning.
  • A name in the right-hand column: looking up "Bag" in A2:C6 fails, because column A holds IDs. VLOOKUP only searches the first column of the block.
  • Column index 4 in a three-column table gives #REF!, since there is no fourth column.
  • A wildcard such as "L*" works with exact match on text: * stands for any characters and ? for one. Use it carefully, since it returns the first match.

Common causes of #N/A

When a lookup you expect to work returns #N/A, check these in order:

  1. The key really is missing. Search the first column with Ctrl+F.
  2. Text versus number. Numbers stored as text (often with a green triangle, or left-aligned) will not match real numbers. =ISNUMBER(E2) tells you which you have, and VALUE() or multiplying by 1 converts text to a number.
  3. Hidden spaces. Trailing spaces, non-breaking spaces from web pages (CHAR(160)) and line breaks. TRIM handles ordinary spaces; CLEAN removes line breaks.
  4. The wrong column holds the key. The table block must start with the key column.
  5. A moving range. If you copied the formula without $ anchors, the table slid down and lost the row.
  6. Approximate match on text where the lookup value sorts before the first key: with TRUE, a value smaller than every key gives #N/A.

The limits of VLOOKUP

VLOOKUP is still fine for small, stable tables. Its design has four limits you should know:

1. It cannot look left. The key column must be the leftmost column of the block, and results come from that column or to its right. If your sheet has Name in column B and ID in column A and you want the ID for a name, VLOOKUP cannot do it (the stand-in showed #N/A). You must rearrange the data or switch to INDEX and MATCH (next lessons).

2. The column number is hard-coded. Excel adjusts ranges when you insert a column, but a 3 is just a number. The second block of output above inserts a Stock column before Price. The block is now A2:D2, the formula still says 3, and it quietly returns the stock instead of the price. A formula that returns a wrong value without an error is the worst kind of failure. You can reduce the risk with MATCH to find the column number (=VLOOKUP(E2, A:D, MATCH("Price", A1:D1, 0), FALSE)) but that is clumsy.

3. It returns only the first match. With duplicate keys, you always get the top row. That is sometimes useful and sometimes a hidden bug.

4. It returns one column per formula. Needing three fields means three formulas, and Excel scans the key column three times. With whole-column or very large ranges, this adds up.

Use FALSE as a reflex. Write VLOOKUP(key, table, col, FALSE) unless you have a sorted band table and mean to use approximate matching.

The same job in pandas

Many analysts move from Excel to Python. A VLOOKUP for a whole column of keys corresponds to a left merge: keep every row of the left table and attach matching columns from the right table. A missing key becomes NaN (Excel's #N/A). The live example below runs real pandas.

Python VLOOKUP as a pandas merge

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.

Notice what the merge makes easy: you can name any column as the key (no "leftmost" rule), you refer to columns by name (no fragile index), and you can list the unmatched rows in one line. The one behavioral difference: when the right table has duplicate keys, merge returns every match, while VLOOKUP returns only the first.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the 3 mean in =VLOOKUP(E2, A2:C6, 3, FALSE)?

    The column index counts columns inside the table block, starting at 1 for the key column. If the block started in column B, a 3 would mean column D.
  2. 2. You leave out the fourth argument on an unsorted list of product names. What is most likely to happen?

    The default for range_lookup is TRUE (approximate match), which assumes a sorted first column. On unsorted data it can return a wrong row with no error.
  3. 3. Which task can VLOOKUP NOT do directly?

    VLOOKUP searches the first column of the block and can only return that column or columns to its right. Looking left needs INDEX/MATCH or XLOOKUP.
  4. 4. A lookup for 103 returns #N/A, yet the ID is visibly in the table. What is a likely cause?

    103 and the text "103" look the same but are different types, so an exact lookup does not match them. Hidden spaces cause the same symptom.

Summary

  • VLOOKUP(key, table, column_number, FALSE) searches the first column of the table and returns a value from the given column.
  • The fourth argument defaults to approximate match; use FALSE for exact lookups, and TRUE only for sorted bands.
  • #N/A usually means a missing key, but numbers stored as text, hidden spaces and a wrong key column are the usual suspects.
  • Limits: no left lookups, a hard-coded column number that breaks when columns move, first match only, one column per formula.
  • A left merge in pandas does the same job by column name.

Next, the approximate mode that makes VLOOKUP shine: HLOOKUP and Approximate-Match Lookups.