VLOOKUP, INDEX MATCH and XLOOKUP, Explained
How VLOOKUP works, why FALSE matters for an exact match, what causes #N/A, and when INDEX MATCH or XLOOKUP is the better lookup in Excel and Google Sheets.
By Udit Agarwal · Updated October 17, 2026
VLOOKUP is a spreadsheet function that searches the first column of a table for a value and returns the value from another column in the same row. In Excel it is written =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), and Google Sheets uses the same pattern. Its two weaknesses are that it only returns columns to the right of the one it searches, and that it matches approximately unless you ask for an exact match. INDEX with MATCH, and the newer XLOOKUP, do the same job without those limits.
| Argument | What it means | Example |
|---|---|---|
| lookup_value | The value to find, usually a cell reference | F2 |
| table_array | The table to search, with the lookup column first | $A$2:$D$6 |
| col_index_num | Which column of the table to return, counting the first as 1 | 3 |
| range_lookup | FALSE or 0 for an exact match, TRUE or 1 for an approximate one | FALSE |
Three details trip people up. The column number counts from the first column of the table, not from column A of the sheet, so in a table that spans B to E, column D is 3. A column number below 1 gives #VALUE!, and one larger than the table is wide gives #REF!. And the table should be locked with dollar signs, as in $A$2:$D$6, so that it does not slide down when you fill the formula into the rows below.
Here is a small price list:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Product | Price | Stock |
| 2 | 101 | Apple | 1.20 | 50 |
| 3 | 102 | Banana | 0.50 | 120 |
| 4 | 103 | Cherry | 4.00 | 0 |
| 5 | 104 | Date | 6.50 | 35 |
| 6 | 105 | Elderberry | 8.25 | 12 |
With 103 typed in F2, this formula returns 4.00:
=VLOOKUP(F2,$A$2:$D$6,3,FALSE)
The spreadsheet runs down column A until it finds 103, in the third row of the table, then moves across to the table's third column, C, and returns the price. Change the 3 to 2 and the same formula returns Cherry; change it to 4 and it returns the stock, 0.
FALSE (or 0) means exact match. VLOOKUP returns the first row whose first column equals the lookup value, ignoring upper and lower case, or #N/A if there is none. In exact mode, text lookups also accept wildcards: * stands for any run of characters, ? for one character, and ~ before either matches it literally.
TRUE (or 1) means approximate match. VLOOKUP assumes the first column is sorted in ascending order and returns the row with the largest value that is less than or equal to the lookup value. That is right for bands, such as grades, tax brackets or shipping weights. If a grade table's first column holds 0, 60, 70, 80 and 90 and the second holds F, D, C, B and A, then =VLOOKUP(78,$H$2:$I$6,2,TRUE) returns C, because 70 is the largest threshold not above 78.
Leaving the argument out means TRUE. Microsoft's documentation confirms that the default is approximate match, and this is the setting that bites. On the price list above, =VLOOKUP(106,$A$2:$D$6,3) returns 8.25, Elderberry's price, for an ID that does not exist, because 105 is the largest ID not above 106. A lookup value smaller than every entry, such as 100, gives #N/A, and on unsorted data the search can land on the wrong row without any error at all. For IDs, codes and names, always type FALSE. The Excel Formula Explainer flags a VLOOKUP that is missing it.
VLOOKUP always searches the first column of the table and can only return columns to its right. To find the ID of "Cherry" on the price list, you would search column B and return column A, to its left, which VLOOKUP cannot express. The fix is to rearrange the columns or switch to INDEX MATCH or XLOOKUP.
A fixed column number causes a second, quieter problem: insert a column inside the table and the range grows to match, but the 3 in the formula stays 3, so the formula now returns the wrong column without any error.
#N/A means "not found", and the value is often there but different in a way you cannot see. The usual causes:
F2*1 turns text into a number and F2&"" turns a number into text.SUBSTITUTE(A2,CHAR(160),"") because TRIM leaves them alone.=IFNA(VLOOKUP(F2,$A$2:$D$6,3,FALSE),"Not found").Prefer IFNA to IFERROR for that last step. IFERROR hides every error, including the #REF! you get from a wrong column number, while IFNA hides only "not found".
INDEX and MATCH split the lookup into two steps. MATCH(lookup_value, lookup_array, [match_type]) returns the position of a value in a single column or row, and INDEX(array, row_num) returns the item at a given position. Put together on the price list:
=INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0))
MATCH finds 103 in position 3 of A2:A6, and INDEX returns position 3 of C2:C6, which is 4.00. Because the return column is named directly, it can sit anywhere. The left lookup that VLOOKUP cannot do is just as easy: =INDEX($A$2:$A$6,MATCH("Cherry",$B$2:$B$6,0)) returns 103. Inserting columns does not break it either.
MATCH has the same trap as VLOOKUP. Its match_type defaults to 1, the largest value less than or equal to the lookup value in an ascending list, and -1 finds the smallest value greater than or equal to it in a descending list. Write 0 for an exact match. The two ranges must also start on the same row and have the same height; if they are offset, INDEX returns a neighboring row. A second MATCH for the column number turns the pair into a two-way lookup across a grid, such as sales by region and quarter.
XLOOKUP is the modern replacement: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). On the price list, =XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6,"Not found") returns 4.00, or the text you chose when nothing matches. It matches exactly by default, looks left or right, and has a match mode of -1 or 1 for the next smaller or larger value without sorted data, and 2 for wildcards. A search mode of -1 searches from the bottom, which returns the last match instead of the first. The XLOOKUP reference lists every option.
| VLOOKUP | INDEX + MATCH | XLOOKUP | |
|---|---|---|---|
| Looks left | No | Yes | Yes |
| Survives inserted columns | No | Yes | Yes |
| Default match | Approximate | Approximate (type 1) | Exact |
| Built-in "not found" value | No | No | Yes |
| Available in | Every version | Every version | Microsoft 365, Excel 2021 and later, Google Sheets |
XLOOKUP is not available in Excel 2019 or earlier, which show a #NAME? error when the formula recalculates. If everyone who opens the file has a current version, use XLOOKUP. If it must also work in older Excel, use INDEX MATCH.
Google Sheets supports VLOOKUP, INDEX, MATCH and XLOOKUP. Its VLOOKUP calls the arguments search_key, range, index and is_sorted, and is_sorted also defaults to TRUE, so write FALSE for an exact match. Wildcards work only in that exact mode. Sheets also accepts open-ended ranges such as A2:D, which Excel does not, and has a QUERY function that Excel lacks.
In countries that write decimals with a comma, both Excel and Sheets separate arguments with semicolons: =VLOOKUP(F2;$A$2:$D$6;3;FALSE). Excel in those languages also translates function names, so VLOOKUP is SVERWEIS in German and BUSCARV in Spanish. The Excel Function Translator converts a formula between languages.
The VLOOKUP Formula Generator asks for the lookup value, the table range, the return column (by number, or by letter converted relative to the start of the table) and exact or approximate match. Options add IFERROR or IFNA with your own text, TRIM, a number-to-text or text-to-number conversion, a starts-with or contains wildcard, dollar-sign locking and comma or semicolon separators. Alongside the VLOOKUP it writes the XLOOKUP and INDEX MATCH equivalents, plus FILTER and QUERY versions for exact matches without a wildcard. It refuses a return column left of the table, pointing you to INDEX MATCH, and catches a column number wider than the table before Excel would show #REF!.
A test sheet of up to 40 rows and 12 columns, preloaded with the price list above, runs your formula with VLOOKUP's rules, including case-insensitive matching, wildcards and the sorted-data search behind approximate match, and warns when the data is not sorted. You can paste rows from Excel or Sheets, and a checklist lists likely causes of #N/A for your settings. The INDEX MATCH Formula Generator covers left lookups, two-way lookups, up to four criteria and the Nth match. Both run in your browser and keep your last settings and test sheet in its local storage; nothing is uploaded.
What is the difference between VLOOKUP and INDEX MATCH? VLOOKUP searches the first column of a table and returns a column to its right by number. INDEX MATCH finds a position with MATCH and returns that position from any range with INDEX, so it can look left and keeps working when columns are inserted. Both work in every version of Excel and in Google Sheets.
Why does my VLOOKUP return #N/A when the value is there? The two values usually differ in a way you cannot see, such as a number stored as text or a trailing space. A table range that shifted when the formula was filled down, and approximate match on unsorted data, are other common causes.
What does FALSE mean at the end of a VLOOKUP? It asks for an exact match. Without it, VLOOKUP uses approximate match, which expects the first column to be sorted and returns the nearest smaller value instead of an error when there is no exact match.
Is XLOOKUP better than VLOOKUP? For most tasks, yes. It matches exactly by default, can return columns on either side, and has a built-in value for "not found". Its drawback is availability: it needs Microsoft 365, Excel 2021 or later, or Google Sheets.
Can VLOOKUP look to the left? No. It always searches the first column of the range and returns columns to its right. Use INDEX MATCH or XLOOKUP when the value you want sits left of the column you search.
Why does VLOOKUP return 0 instead of a blank? In Excel, the matching row's cell in the return column is empty, and VLOOKUP shows an empty result as 0. The lookup itself worked; fill in the missing value, or test for an empty result with an IF around the formula.
All Excel & Spreadsheets tools · All guides