## What You'll Learn / Key Take-Aways

- XLOOKUP is a modern lookup function for Office 365 Excel that replaces many VLOOKUP and HLOOKUP scenarios.

- Core syntax: `XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`.

- XLOOKUP can return multiple columns as a dynamic array, search left or right, and supports built-in error handling.

- Optional match modes include exact, approximate, and wildcard text matching; search modes let you find the first or last match.

- XLOOKUP defaults to exact match and works with unsorted lists for approximate matches, removing many classic VLOOKUP limitations.

## What XLOOKUP does

XLOOKUP retrieves a value or values from a table or range by matching a lookup value. If you used VLOOKUP, HLOOKUP, or INDEX and MATCH, XLOOKUP provides a simpler, more capable replacement. Key differences are its ability to:

- Return values from anywhere in the table, not just to the right of the lookup column.

- Return entire ranges or multiple columns with a single formula thanks to dynamic arrays.

- Provide built-in error handling so you do not need `IFERROR` wrappers.

- Support flexible match and search modes, including first or last match.

XLOOKUP is currently available to Office 365 subscribers. If you use an older or standalone copy of Excel, it may not be available.

## XLOOKUP syntax and what each argument means

The basic syntax is:

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

- lookup_value: The value you want to find.

- lookup_array: The single row or single column to search for the lookup value.

- return_array: The single row or single column from which to return the matching value or values.

- if_not_found (optional): Value to return if no match is found. This replaces common IFERROR patterns.

- match_mode (optional): Controls match behavior. Values include exact match, exact match or next smaller, exact match or next larger, and wildcard matching for text.

- search_mode (optional): Controls search order. Values include search from first to last, last to first, binary search on sorted data, and reverse binary search.

Note that lookup_array and return_array must be the same length and can be arranged horizontally or vertically. Unlike VLOOKUP, the return array does not need to be to the right of the lookup array.

## Practical examples you can try

1) Exact match returning a single value

- Formula: `=XLOOKUP(A2, B:B, C:C)`

- Looks up the value in A2 in column B and returns the corresponding cell from column C. Defaults to exact match.

2) Return a friendly message when not found

- Formula: `=XLOOKUP("Widget-123", A:A, D:D, "Not found")`

- If the code is missing, the formula returns "Not found" instead of an error.

3) Return multiple columns (dynamic array spill)

- Formula: `=XLOOKUP(E2, A:A, B:D)`

- If you return B:D, XLOOKUP spills the matching row across multiple columns. This is dynamic array functionality in Office 365.

4) Wildcard text match

- Formula: `=XLOOKUP("*Widget*", A:A, B:B, "Not found", 2)`

- Use a match_mode that supports wildcards to find partial matches. Check exact match mode numeric codes in Excel help.

5) Find the last occurrence

- Formula: `=XLOOKUP(G2, A:A, B:B, "Not found", 0, -1)`

- Use a search_mode set to reverse search to find the last match instead of the first.

## Match mode and search mode explained

Match mode decides how XLOOKUP treats lookup values:

- Exact match is the default and is what most users need.

- Approximate match can find nearest values without requiring a sorted list, a key improvement over old VLOOKUP approximate behavior.

- Wildcard matching lets you use `*` and `?` for partial text matches.

Search mode controls direction and type of search:

- Normal search finds the first match from top to bottom or left to right.

- Reverse search finds the last match.

- Binary search options are available for very large sorted datasets and can be faster but require the array to be sorted appropriately.

## When to choose XLOOKUP instead of VLOOKUP or INDEX/MATCH

Choose XLOOKUP when you need any of the following:

- Lookups to the left of the key column.

- Multiple column returns with one formula.

- Built-in error handling without wrapping IFERROR.

- Wildcard or flexible search behavior.

- A simpler, more readable formula than nested INDEX/MATCH.

You may still use VLOOKUP if you are on older Excel versions or for quick legacy sheets, but migration to XLOOKUP is recommended once Office 365 is available.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- Availability: XLOOKUP requires Office 365. Check Excel version before sharing files.

- Dynamic array spill: When returning multiple columns, ensure the spill range is clear of values; otherwise Excel returns a spill error.

- Performance: For very large datasets, test search modes. Binary search is fast but requires sorting.

- Compatibility: If you must support older Excel users, keep fallback formulas or provide helper columns.

- Use `if_not_found` to provide meaningful messages and avoid #N/A errors in dashboards.

## FAQ

Q: Is XLOOKUP available in my version of Excel?

A: XLOOKUP is available to Office 365 subscribers. Standalone or older perpetual versions of Excel may not have it. Check your Excel update channel.

Q: How is XLOOKUP different from VLOOKUP?

A: XLOOKUP can search left or right, return multiple columns, provide built-in error handling, default to exact match, and work with unsorted data for approximate matches. VLOOKUP can only search to the right and returns a single column value.

Q: Can XLOOKUP return multiple columns at once?

A: Yes. If you provide a multi-column return_array, XLOOKUP will spill the results across columns using dynamic array behavior in Office 365.

Q: How do I handle missing values with XLOOKUP?

A: Use the optional `if_not_found` argument to return a custom message or value instead of an error. This replaces the need for IFERROR wrappers in many cases.

Q: Can XLOOKUP do wildcard searches or find the last match?

A: Yes. Use the match_mode argument for wildcard text matching and the search_mode argument to search in reverse to find the last occurrence.

## Summary of Key Points / Take-Home Messages

- XLOOKUP replaces many VLOOKUP and HLOOKUP use cases and simplifies INDEX/MATCH formulas.

- It returns dynamic arrays, supports leftward lookups, and has built-in error handling.

- Match and search modes provide flexible matching and direction control, including wildcards and last-match searches.

- XLOOKUP is available for Office 365 users. Test compatibility before deploying workbooks to users on older Excel.

Next step: open Excel on Office 365 and try the examples above. In the next lesson we will walk through live demos and show each match and search mode in action.