Excel beginner xlookup vlookup formulas data-retrieval

How does XLOOKUP compare to VLOOKUP?

XLOOKUP vs VLOOKUP Comparison is a Excel function that retrieves a value from a specified array based on a matching value from another array.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

XLOOKUP offers enhanced functionality over VLOOKUP, making it easier to retrieve data. Here, we show you how to convert VLOOKUP formulas to XLOOKUP.

Generate This Formula Browse Library

The Formula

Prompt

"Compare XLOOKUP and VLOOKUP side by side, showing how to convert a VLOOKUP formula to XLOOKUP with the same result"

Excel 
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

This formula retrieves a value from a specified array based on a matching value from another array.

Step-by-Step Breakdown

  1. lookup_value: the value you want to find.
  2. lookup_array: the range where the lookup_value is searched.
  3. return_array: the range from which to return the corresponding value.
  4. if_not_found: optional value to return if no match is found.
  5. match_mode: optional argument to specify exact or approximate match.

Edge Cases & Warnings

  • If the lookup_value is not found, XLOOKUP can return a custom message.
  • Using XLOOKUP with multiple criteria may require combining values.
  • XLOOKUP does not require the lookup array to be on the left of the return array.

Examples

Prompt

"VLOOKUP(A2, B2:D10, 3, FALSE)"

Excel 
Returns the value from the third column where A2 matches the first column.
Prompt

"XLOOKUP(A2, B2:B10, D2:D10, 'Not Found')"

Excel 
Returns the corresponding value from D2:D10 or 'Not Found' if A2 is not in B2:B10.

Frequently Asked Questions

What are the main advantages of XLOOKUP over VLOOKUP?

XLOOKUP allows for both vertical and horizontal lookups, handles errors more gracefully, and does not require the lookup column to be on the left.

Can XLOOKUP replace all VLOOKUP use cases?

Yes, XLOOKUP can handle all scenarios that VLOOKUP can, often with more flexibility.

Is XLOOKUP available in all versions of Excel?

XLOOKUP is available in Excel 365 and Excel 2021, but not in earlier versions.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples