edubun
English
English한국어EspañolPortuguêsFrançaisDeutschItalianoРусский
Home›Spreadsheets and data›Spreadsheet formulas

Looking things up with VLOOKUP

Leave out the last argument of VLOOKUP and both Excel and Google Sheets use an approximate match. Google recommends typing FALSE.

A price list with codes

A shop keeps its prices in a small table: codes in A2:A5, item names in B2:B5, prices in C2:C5. At the till, someone types a code and wants the price. That is VLOOKUP's job. It finds a value in the first column of a table and returns something from the same row. Microsoft lists the four pieces it needs: the value to look up, the range it is in, the column number in that range that holds the answer, and whether you want an approximate (TRUE) or exact (FALSE) match. =VLOOKUP(104, A2:C5, 3, FALSE) finds 104 in column A and returns the price from the third column of the range.

Two rules cause most of the trouble. First, the value must be in the first column of the range, and the answer must be to its right. VLOOKUP never looks left, so if the codes are in column B, the range has to start at B. Second, the column number counts from the first column of the range, not from column A of the sheet; in B2:D9, B is 1, C is 2 and D is 3. Both help pages give the same advice for copied formulas too. Make the range absolute, as in $A$2:$C$5, so it doesn't slide when filled down.

When the code isn't on the list

With FALSE, VLOOKUP looks for exactly the value you gave and returns the first row that matches. No match gives #N/A. A column number larger than the number of columns in the range gives #REF!, and one below 1 gives #VALUE!. Text lookups ignore case but not spaces: Google's help points out that " Apple", "Apple " and "Apple" are three different values. A number stored as text differs from the number too, so a code typed as '104 is not found by 104.

With exact match, the wildcards ? and * work in both programs. ? stands for any one character and * for any run of characters, so "Ora*" finds Orange. When #N/A is a normal outcome, such as a code not yet on the price list, wrap the lookup in IFNA, which both programs have: =IFNA(VLOOKUP(E2, $A$2:$C$5, 3, FALSE), "not found"). IFERROR also works. It hides every kind of error, though, including a wrong column number you would want to see, and Microsoft's #VALUE! page warns about IFERROR for the same reason.

Loading

Drag to turn. Scroll or pinch to zoom.

After the tasks, type 102 into E2: exact match gives #N/A. Then change the column number to 4 and read the #REF! explanation.

Looking up a band instead of an exact code

TRUE, or no fourth argument at all, gives an approximate match, the default in both programs. Google's help describes the search: it runs down the first column until it finds a value larger than the search key, then stops on the row before. That suits bands. In a grade table with 0, 60, 70, 80 and 90 in the first column, a score of 85 runs past 80, meets 90, and returns B from the 80 row. The first column must be sorted from smallest to largest, and a value below the first row returns #N/A. On an unsorted column the answer can't be trusted, which is why Google recommends FALSE unless you mean a range.

Loading

Drag to turn. Scroll or pinch to zoom.

The same grade scale as the nested IF, kept in cells. Swap two rows of the table afterwards and the panel warns that the first column is no longer sorted.
Microsoft ExcelGoogle Sheets
Name of the 4th argumentrange_lookupis_sorted
Default when left outTRUE (approximate)TRUE (approximate)
Advice in the help pagesort the first column, or use FALSEFALSE is recommended
XLOOKUPMicrosoft 365 and Excel 2021 onward; not in 2016 or 2019available
Unreadable formularefused with a message#ERROR!
Circular referencewarning, then 0 or the last value#REF!
ROUND without digitsnot acceptedrounds to a whole number
Arguments per functionup to 255any number

Both programs now also have XLOOKUP. It takes a lookup range and a separate result range, so the answer may sit to the left, and it uses an exact match unless told otherwise. Microsoft notes that XLOOKUP is not available in Excel 2016 or Excel 2019, so a file that must open in those versions should stay with VLOOKUP. The table above collects the differences between the two programs that this course has met.

Common mistakes
  • Leaving out FALSE when looking up a code. The default is approximate match, so a code that isn't on the list can come back with the price from the row above it instead of #N/A.
  • Counting the column number from column A of the sheet. It counts from the first column of the range. With C2:F20, column E is 3, not 5.
  • Filling a lookup down without $ on the range. A2:C5 slides to A3:C6, A4:C7 and so on, and lower rows stop finding codes near the top of the table.
  • Wrapping every lookup in IFERROR. It hides a #REF! from a wrong column number as well as the expected #N/A. IFNA hides only #N/A.
Teacher's noteVLOOKUP has four arguments, and learners tend to lose track of the third and fourth. Have them say the four pieces aloud for every formula they write. Teach FALSE first and treat approximate match as a separate lesson with a band table, so the two ideas don't blur. Then break the lab on purpose: a missing code, a column number that's too big, a code with a trailing space. An error message is far easier to remember once you have caused it by hand. XLOOKUP is worth a mention if everyone uses Microsoft 365 or Sheets.
  1. Codes 101, 103, 104, 107 with items Apple, Banana, Orange, Pear in A2:B5. What does =VLOOKUP(103, A2:B5, 2, FALSE) return?
  2. Same table. What does =VLOOKUP(102, A2:B5, 2, FALSE) return?
    • #N/A
    • Apple
    • Banana
    • #REF!
  3. Same table. What does =VLOOKUP(102, A2:B5, 2, TRUE) return?
  4. Same table. What does =VLOOKUP(103, A2:B5, 3, FALSE) return?
    • #REF!
    • #N/A
    • #VALUE!
    • Banana
  5. The range is C2:F20. What column number returns values from column E?
  6. Grade table: 0 F, 60 D, 70 C, 80 B, 90 A. What does an approximate VLOOKUP return for 85?
  7. Same table. What does it return for exactly 90?
  8. What happens when the fourth argument is left out?
    • Approximate match in both programs
    • Exact match in both programs
    • Exact in Excel, approximate in Sheets
    • An error
  9. Why should the table range be written as $A$2:$C$5?
    • So it does not slide when the formula is filled down
    • So it sorts the table
    • So it matches upper case only
    • So it returns text
  10. Items are in B2:B5 and prices in C2:C5. What does =IFNA(VLOOKUP("Pencil", B2:C5, 2, FALSE), "not found") return?
  11. Which Excel versions do not have XLOOKUP?
    • Excel 2016 and Excel 2019
    • Microsoft 365
    • Excel for the web
    • Excel 2021
Key points
  • VLOOKUP(value, range, column number, FALSE) for an exact match.
  • The value must be in the first column; the column number counts from the range.
  • TRUE (or nothing) is approximate: the first column must be sorted, and it returns the last row not larger than the value.
  • Use an absolute range, IFNA for expected #N/A, and FALSE unless you mean bands.
Sources for this course (19)
  • Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
  • Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
  • Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
  • Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
  • Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
  • Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
  • Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
  • Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
  • Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
  • Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
  • Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
  • Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
  • Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
  • Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
  • Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
  • Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
  • Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
  • Ablebits (IF without value_if_false returns FALSE)
  • Spreadsheet Class (Sheets circular dependency shows #REF!)

Checked: 2026-10-19