How To Use XLOOKUP And VLOOKUP In Excel (2026)
XLOOKUP and VLOOKUP both find a value in one column and return a matching value from the same row.
Microsoft now recommends XLOOKUP, but VLOOKUP is still common in older workbooks, so it helps to read both.
This guide shows the syntax, the exact-match settings and the fixes for #N/A, based on Microsoft’s support pages.
Quick answer: which function to use
Use XLOOKUP if your Excel has it, and use VLOOKUP when you must stay compatible with older versions.
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Default match | Exact match | Approximate match when the last argument is omitted |
| Lookup direction | Any direction, so the lookup column can sit left or right of the result | Left to right only, with the lookup value in the first column |
| Return several columns | Yes, in one formula | No |
| Not-found message | Built-in if_not_found argument | Needs a wrapper such as IFERROR |
| Available in Excel 2016 and 2019 | No, according to Microsoft | Yes |
XLOOKUP syntax
The formula has three required arguments and three optional ones.
Microsoft describes it as searching a range or an array and returning the item for the first match it finds.
| Argument | Required | What it does |
|---|---|---|
| lookup_value | Yes | The value to search for |
| lookup_array | Yes | The range or array to search |
| return_array | Yes | The range or array to return |
| if_not_found | No | Text to show when there is no match; the default is #N/A |
| match_mode | No | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard |
| search_mode | No | 1 first to last (default), -1 last to first, 2 or -2 binary search |
The full form is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
How to write an XLOOKUP, step by step
- Click the cell where you want the answer.
- Type
=XLOOKUP(and click the cell that holds the value you want to find. - Type a comma, then select the column that contains that value as the lookup array.
- Type a comma, then select the column that holds the answer as the return array.
- Close the bracket and press Enter.
Because exact match is the default, you can leave out match_mode for most jobs.
XLOOKUP examples from Microsoft
Microsoft’s page includes formulas that cover the most common needs.
| Goal | Formula pattern |
|---|---|
| Return one value | =XLOOKUP(F2,B2:B11,D2:D11) looks up a country in B2:B11 and returns its code from D2:D11 |
| Return two columns | =XLOOKUP(B2,B5:B14,C5:D14,0,1) returns the name and department for an employee ID |
| Show custom text | Add a message in the fourth argument, such as "Employee not found" |
| Two-way lookup | Nest one XLOOKUP inside another to match a row and a column |
VLOOKUP syntax
VLOOKUP needs four pieces of information.
The form is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
- lookup_value is the value to find, and it must be in the first column of the range.
- table_array is the range to search, and it must include the column with the answer.
- col_index_num is the column number inside that range, counting from 1 at the left.
- range_lookup is TRUE or 1 for approximate match, or FALSE or 0 for exact match.
Always choose exact match with VLOOKUP
If you leave the last argument out, VLOOKUP assumes TRUE and does an approximate match.
An approximate match assumes the first column is sorted and returns the closest value.
For IDs, names and codes you almost always want FALSE.
For example, =VLOOKUP(102,A2:C7,2,FALSE) looks up ID 102 in column A and returns the last name from column B.
XLOOKUP avoids the trap because exact match is its default.
Convert a VLOOKUP into an XLOOKUP
Microsoft shows =VLOOKUP(F2,B2:D11,3,FALSE) as the equivalent of an XLOOKUP that searches B2:B11 and returns D2:D11.
The column number disappears, and you select the return column directly.
That makes the formula easier to read, because you point at the return column instead of counting columns.
When the lookup column is not on the left
VLOOKUP only looks to the right, so a lookup value in a later column fails.
Microsoft’s older advice is to use INDEX and MATCH in that case.
MATCH finds the position of the value, and INDEX returns the item at that position from another column.
XLOOKUP now handles this directly, so use it where you can.
Fix #N/A and wrong results
Microsoft lists the same few causes for lookup failures.
| Symptom | Cause | Fix |
|---|---|---|
| #N/A with FALSE or exact match | The value is not in the first column, or has a typo | Check the spelling and that the value exists in the source |
| #N/A with TRUE | The lookup value is smaller than the smallest value in the first column | Use FALSE, or sort the column and check the value |
| Wrong value returned | Approximate match on an unsorted column | Sort the first column or use FALSE |
| #N/A on matching-looking values | Number in one place and text in the other | Make both the same data type |
| #N/A on identical text | Extra leading or trailing spaces | Wrap the range in TRIM |
| #REF! | col_index_num is larger than the number of columns in table_array | Lower the column number |
| #NAME? | Text in the formula without quotation marks | Write "Fontana" rather than Fontana |
Show a friendly message instead of an error
In XLOOKUP, put your message in the fourth argument.
In VLOOKUP, wrap the formula in IFERROR, as Microsoft’s #N/A page shows with =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,FALSE),0).
You can return empty text or a custom message instead of 0.
Use this only when missing values are expected, because it also hides real mistakes.
Tips that prevent lookup problems
- Use absolute references such as $A$2:$C$20 so a filled-down formula keeps searching the same range.
- Do not store numbers or dates as text in the lookup column.
- Clean the data with TRIM and CLEAN to remove extra spaces and nonprinting characters.
- With FALSE and text lookups, wildcards work: ? matches one character and * matches any run of characters.
- Binary search modes in XLOOKUP need sorted data, or the results are invalid.
- In XLOOKUP, match_mode 2 turns on wildcards, where *, ? and ~ have special meaning.
- If you leave out the lookup value in XLOOKUP, it returns any blank cells it finds in the lookup array.
- A workbook that contains XLOOKUP formulas can still be opened in Excel 2016 and 2019, according to Microsoft, but you cannot write new XLOOKUP formulas there.
Related Excel guides
See our guide to fixing #N/A, #VALUE! and #REF! errors in Excel for the other error types.
If a formula shows as text instead of a result, read why Excel formulas are not calculating.
To tidy a lookup list first, try removing duplicates in Excel.