The **Excel XLOOKUP function** replaces the older lookup functions like VLOOKUP, HLOOKUP and supports lookups for horizontal a vertical ranges.

**Table of contents:**

**What is XLOOKUP****How can I get XLOOKUP****How to use Excel XLOOKUP function****Syntax**,**Arguments****XLOOKUP Function examples****Why XLOOKUP is better than other lookups**

## What is XLOOKUP?

XLOOKUP is the latest lookup function in Excel and sends a “Happy Retirement” message to the older lookup functions, like VLOOKUP, **HLOOKUP**, LOOKUP, and the INDEX+MATCH combination.

**XLOOKUP is similar to VLOOKUP but uses six arguments.** Three arguments are required, another three are optional.

**The value you are looking for:**lookup_value**The list where we find the lookup value:**lookup_array**The list from which you want the result:**return_array

**Optional arguments:**

**Error handling;**what if the value is not found: not_found**Declare match types:**match_mode**Controls the search action**: search_mode

### How can I get XLOOKUP

XLOOKUP is only available for the Microsoft365 (formerly Office365) users. If you are using an older version of Microsoft Excel (2010, 2013, 2016, 2019), you cannot use the function.

That was the official information, but it is tricky to use it with any Excel versions.

**If you want to use XLOOKUP with the recent Excel versions, check our add-in **that provides the latest lookup functionality for all Excel versions.

## How to use XLOOKUP Function

**XLOOKUP lookup values in a range or array and returns matching value(s) from the return array.**

### Syntax

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

### Arguments

Here is some basic example.

### Basic usage (using three arguments)

=XLOOKUP(what you want to find, lookup list, result list)

=XLOOKUP(“Banana”, B3:B6, D3:D6) returns Banana if the name can be found in column B.

For the sake of simplicity, **use named ranges** and add “**Fruits**” to range B3:B6 and “**Price**” to range D3:D6.

### Using the “if not found” argument (4th argument of XLOOKUP)

For example, we want to find the price of the Mango, use the formula:

=**XLOOKUP**(Mango”, Fruits, Price, ”Not found”) **return “Not found” if the lookup value (Mango) is not available in column B.**

### Match mode or type (5th argument)

Use match mode as a 5th argument of XLOOKUP to **declare Excel how you want your MATCH to happen.** These are the following:

Match type | Action |
---|---|

0 (default) | Exact match (returns #N/A if no match.) |

-1 | Returns exact match or the next smaller item. |

1 | Gets an exact match or the next larger item. |

2 | Wildcard match |

### Search mode (6th argument)

Good to know that **XLOOKUP will start matching from the first data value by default**. The search_mode optional argument controls the search action and uses the following options:

Search Mode | Action |
---|---|

1 (default) | Search from the first value |

-1 | Search from last value (reverse) |

2 | Binary search [values sorted in ascending order] |

-2 | Binary search [values sorted in descending order] |

## XLOOKUP Function Examples

If your Excel version supports XLOOKUP, **download the sample workbook** and play with it.

Use names for a range to make your learning curve fast and easier. For example, XLOOKUP works fine if you use classic cell references, but **structured references** provide easy-to-understand formulas.

**Before we take a deep dive into XLOOKUP, prepare your table, it can take seconds. First, select** the range **you want to use and add a name. **

**Next, locate the name box, enter a descriptive name, and press Enter.**

We’ll use the following named ranges:

**Location**(range B3:B13)**Orders**(range C3:C13)**Sales**(range D3:D13)**Cost**(range E3:E13)

### Example #1 – Basic Exact Match

In the example, we’ll use only the first three arguments of the XLOOKUP function.** First, you want to know the sales for Delaware.**

**By default, XLOOKUP uses exact match.** Therefore, in the example below, XLOOKUP is used to retrieve sales (from column D) based on an exact match on location.

Formula:

=**XLOOKUP(H3, Location, Sales)**

### Example #2 – Lookup right to left

In the example, we’ll show you **how to replace the old INDEX + MATCH combination if you need to use left lookup**.

Now we find the location where the sales are 9460. In this case, the lookup array is D3:D13 (Sales), the return array is B3:B13 (Location).

Formula:

**=XLOOKUP(H3, Sales, Location)**

### Example #3 – Combine XLOOKUP with the MIN function

In the example, we’ll combine the XLOOKUP function with the MIN function to **get the minimum sales and the correspondent location.**

Formula:

**=XLOOKUP(MIN(Sales), Sales, Location)**

In this case:

- lookup value:
**MIN(Sales)** - lookup array:
**Sales** - return array:
**Location**

### Example #4 – XLOOKUP replaces INDEX + MATCH

Let us another example! We **find the location where the corresponding order = 160.**

Formula:

**=XLOOKUP(H3, Orders, Location)**

### Example #5 – Specify match type and search order

In the example, we’ll use two optional arguments for XLOOKUP to specify match type (0 is for exact match) and match direction (-1 is for the bottom to top).

Formula:

**=XLOOKUP(H3, Location, Cost,,0,-1)**

We use two (**5th – match mode and 6th – search mode**) of the three optional arguments. Parameters are comma-separated-values, so if the 4th parameter is not necessary, type a comma, then add the 5th and 6th argument to XLOOKUP.

### Example #6 – Find the closest value (next smaller item)

We’ll demonstrate how to find the closest value. First, the XLOOKUP function searches for an exact match. If not found, XLOOKUP returns the next smaller item if you use the -1 parameter as a match mode.

Formula:

=**XLOOKUP(H3, Sales, Location,,-1)**

Take a closer look at the result! For example, in cell D9, the value is 10252, less than the lookup value. But it’s the closest value!

### Example #7 – Find the closest value (next larger item)

Let us find the closest value but change the 5th parameter to 1. **In this case, the XLOOKUP function searches for an exact match, but if the exact match is not found, XLOOKUP returns the next larger item.**

Change the match mode to = 1.

Formula:

**=XLOOKUP(H3, Sales, Location, ,1)**

### Example #8 – Wildcard character match (*)

We’ll show you how to use the **XLOOKUP wildcard search function in the following example**.

**Okay, look up the location that begins with “Ne” and returns the orders.** **Use “*” for any number of letters.**

**Formula:**

=**XLOOKUP(H3&”*”,Location,Orders,,2)**

**The most important thing: **set the 5th parameter of the XLOOKUP function to 2 if you are using wildcard character search.

Result:

### Example #8 – Wildcard character match (?)

In the example, we’ll use the “?” symbol to replace a single letter in a word. Don’t forget that we need to use 2 as a parameter for match mode in this case.

Formula:

**=XLOOKUP(H3, Location, Sales,,2)**

### Example #9 – Search last to first

Finally, let us see how to use the 6th argument of the XLOOKUP function. Find the cost for the last item on the list!

Formula:

=**XLOOKUP(“*”, Location, Sales,,2,-1)**

## Why XLOOKUP is better than other lookups

**Key advantages of using XLOOKUP:**

**XLOOKUP reduces formula errors:**Type XLOOKUP(value_to_find, where_to_find, the result list) and job done. If the value is not found, the formula returns with #N/A.**XLOOKUP**handle**left lookup**problems and replaces the VLOOKUP + CHOOSE workaround.**Returns exact match by default.**The most frustrating thing about using HLOOKUP and VLOOKUP is the last parameter. You have to use FALSE to get the correct result. On the other hand, XLOOKUP returns with the exact match by default.**XLOOKIP supports a 4th parameter: the “value not found” scenario**. From now, error handling is much better. Instead of using**IFERROR**or**IFNA**functions, you can add the default output if the value is not found.**XLOOKUP uses an optional 6th parameter for special searches**and supports wildcard, top, and bottom searches.

Thanks for being us today! Stay tuned and follow our latest function guides and tutorials!

**Additional resources**