Excel beginner xlookup exact match lookup data retrieval

How to Use XLOOKUP for Exact Matches in Excel

XLOOKUP Exact Match is a Excel function that searches for an exact match of a specified value in a range and returns a corresponding value from another range, or a custom message if not found.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Struggling to find exact matches in your data? XLOOKUP simplifies this process and allows for custom messages when values aren't found.

Generate This Formula Browse Library

The Formula

Prompt

"Use XLOOKUP to find an exact match and return a value from a different column, with a custom 'not found' message"

Excel 
=XLOOKUP(lookup_value, lookup_array, return_array, "Not Found", 0)

This formula searches for an exact match of a specified value in a range and returns a corresponding value from another range, or a custom message if not found.

Step-by-Step Breakdown

  1. lookup_value: the value you want to find.
  2. lookup_array: the range where you want to search for the lookup_value.
  3. return_array: the range from which to return the corresponding value.
  4. "Not Found": the message displayed if the lookup_value is not found.
  5. 0: specifies that the match must be exact.

Edge Cases & Warnings

  • The lookup_value is not present in the lookup_array.
  • The lookup_array contains duplicate values.
  • The return_array is of a different size than the lookup_array.
  • The lookup_value is of a different data type than the values in the lookup_array.

Examples

Prompt

"XLOOKUP("Apple", A2:A5, B2:B5, "Not Found") where A2:A5 contains {"Apple", "Banana", "Cherry", "Date"} and B2:B5 contains {10, 20, 30, 40}"

Excel 
10
Prompt

"XLOOKUP("Grape", A2:A5, B2:B5, "Not Found") where A2:A5 contains {"Apple", "Banana", "Cherry", "Date"} and B2:B5 contains {10, 20, 30, 40}"

Excel 
"Not Found"

Frequently Asked Questions

What happens if there are multiple matches?

XLOOKUP will return the first match it finds.

Can I use XLOOKUP with text values?

Yes, XLOOKUP works with both text and numeric values.

Is XLOOKUP available in all versions of Excel?

XLOOKUP is available in Excel 365 and Excel 2021.

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