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.

FeatureXLOOKUPVLOOKUP
Default matchExact matchApproximate match when the last argument is omitted
Lookup directionAny direction, so the lookup column can sit left or right of the resultLeft to right only, with the lookup value in the first column
Return several columnsYes, in one formulaNo
Not-found messageBuilt-in if_not_found argumentNeeds a wrapper such as IFERROR
Available in Excel 2016 and 2019No, according to MicrosoftYes

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.

ArgumentRequiredWhat it does
lookup_valueYesThe value to search for
lookup_arrayYesThe range or array to search
return_arrayYesThe range or array to return
if_not_foundNoText to show when there is no match; the default is #N/A
match_modeNo0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard
search_modeNo1 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

  1. Click the cell where you want the answer.
  2. Type =XLOOKUP( and click the cell that holds the value you want to find.
  3. Type a comma, then select the column that contains that value as the lookup array.
  4. Type a comma, then select the column that holds the answer as the return array.
  5. 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.

GoalFormula 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 textAdd a message in the fourth argument, such as "Employee not found"
Two-way lookupNest 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.

SymptomCauseFix
#N/A with FALSE or exact matchThe value is not in the first column, or has a typoCheck the spelling and that the value exists in the source
#N/A with TRUEThe lookup value is smaller than the smallest value in the first columnUse FALSE, or sort the column and check the value
Wrong value returnedApproximate match on an unsorted columnSort the first column or use FALSE
#N/A on matching-looking valuesNumber in one place and text in the otherMake both the same data type
#N/A on identical textExtra leading or trailing spacesWrap the range in TRIM
#REF!col_index_num is larger than the number of columns in table_arrayLower the column number
#NAME?Text in the formula without quotation marksWrite "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.

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.

Sources

Leave a Reply

Your email address will not be published. Required fields are marked *