Excel VLOOKUP and XLOOKUP

VLOOKUP and XLOOKUP both search for a value in one place and return a related value from another, but XLOOKUP removes several of VLOOKUP's long-standing limitations.

What VLOOKUP Does

VLOOKUP searches down the first column of a range for a value you specify, then returns a value from a column you choose to the right of it. It is built for tables where the thing you are searching for sits in the leftmost column.

Example

=VLOOKUP(A2,D:E,2,FALSE)

The Four Arguments of VLOOKUP

ArgumentDescription
lookup_valueThe value you are searching for, such as a cell reference like A2.
table_arrayThe range that contains both the search column and the return column, for example D:E.
col_index_numWhich column inside table_array to pull the result from, counted from 1 starting at the search column.
range_lookupFALSE (or 0) for an exact match; TRUE (or 1) for an approximate match against sorted data.
Note: VLOOKUP can only search the first column of table_array and return values from columns to its right. It cannot look leftward, so if the value you need sits in a column before your search column, VLOOKUP will not reach it directly.

Setting range_lookup to FALSE forces an exact match and returns #N/A if nothing matches, which is what you want for lookups like employee IDs or product codes. Leaving it as TRUE performs an approximate match, but that only behaves correctly if the search column is sorted in ascending order.

XLOOKUP: A More Flexible Replacement

XLOOKUP, available in Excel 365 and Excel 2021 and later, separates the search range from the return range instead of forcing them into one table_array. Its core syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).

Example

=XLOOKUP(A2,D:D,E:E)
  • XLOOKUP can look in any direction: the return column can sit to the left or right of the search column.
  • There is no col_index_num to count, so inserting or deleting columns between the search and return ranges will not break the formula the way it can with VLOOKUP.
  • An optional fourth argument lets you supply a custom result, such as "Not Found", instead of an #N/A error.
  • XLOOKUP defaults to an exact match, whereas VLOOKUP defaults to an approximate match if you omit the last argument.
Note: If your version of Excel does not have XLOOKUP, VLOOKUP paired with FALSE for an exact match remains a reliable choice; just remember the search value must be in the leftmost column of the range you point it at.

Keep Going

Excel Interview QuestionsPractice real questions asked for ExcelData Analyst SalarySee what Data Analyst roles pay in the marketAuto Apply for JobsApply to open roles related to Excel and tweak your resume