What is an xlookup in excel

Answer

XLOOKUP is a lookup function that searches a range or array for a specified value and returns a corresponding value from another range or array. It is designed to replace older lookup functions by working in any direction, returning exact matches by default, and offering flexible handling of not-found results, match types, and search direction. 

Syntax (what the function looks like)

The function signature is:

\texttt{=XLOOKUP(lookup\_value,\ lookup\_array,\ return\_array,\ [if\_not\_found],\ [match\_mode],\ [search\_mode])}

Use the arguments as follows:

1. lookup_value — value to find.

2. lookup_array — range or array to search.

3. return_array — range or array containing values to return.

4. [if_not_found] — value to return when no match exists (optional).

5. [match_mode] — 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match (optional).

6. [search_mode] — 1 search first-to-last (default), -1 search last-to-first, 2 binary search ascending, -2 binary search descending (optional).

How does XLOOKUP differ from older lookup functions?

1. XLOOKUP can return values to the left or right of the lookup column; VLOOKUP cannot return left without rearranging data. 

2. XLOOKUP defaults to an exact match, reducing accidental wrong matches that happen with VLOOKUP’s approximate default. 

3. XLOOKUP supports a built-in if_not_found argument, so error-trapping is simpler than wrapping functions in IFERROR or IFNA. 

4. XLOOKUP can return whole arrays (multiple results) and perform horizontal or vertical lookups with the same function. 

Example — simple price lookup

1. Suppose product names are in A2:A100 and prices are in B2:B100.

2. To find the price for the product typed in cell D2, use:

   \texttt{=XLOOKUP(D2,\ A2:A100,\ B2:B100,\ "Not found")}

This returns the matching price or the text "Not found" when no match exists.

What versions and platforms support XLOOKUP?

XLOOKUP is available in Excel for Microsoft 365 and Excel 2021, and in Excel for the web and modern Excel apps on tablets and phones. XLOOKUP is not available in older perpetual versions such as Excel 2016 and Excel 2019, unless an add-in or compatibility solution is installed. Expect the same availability across Windows and Mac when running Microsoft 365 or Excel 2021.

What to do when XLOOKUP is unavailable on your Excel version

1. Use INDEX() and MATCH() together to replicate XLOOKUP capabilities (exact matches, left-lookups). 

2. Wrap INDEX/MATCH with IFNA() or IFERROR() for custom not-found behavior. 

3. Consider an add-in that provides XLOOKUP-like functions for older Excel builds if upgrading is not possible. 

Common errors and what they mean

1. #NAME? — The function is unknown because the Excel version does not support XLOOKUP. 

2. #N/A — No match found and no if_not_found argument supplied. Use if_not_found to return a friendly message. 

Yes · 0 No · 0 Add Comment · 0

Add ·

Report