## How to Use the NEW & ADVANCED XLOOKUP Function in Excel (10 Examples)

Excel’s new **XLOOKUP** is the most powerful and easiest Lookup function Excel can offer. Through unremitting efforts, Microsoft finally released this XLOOKUP function to replace VLOOKUP, HLOOKUP, INDEX+MATCH, and other lookup functions.

In this tutorial, we will show you **what XLOOKUP’s advantages are** and **how can you get it and apply it** to solve different lookup problems.

**How to Get XLOOKUP?**

Since **XLOOKUP function** is **only available** in **Excel for Microsoft 365**, **Excel 2021**, and **Excel for the web**, you can upgrade your Excel to the available version to get XLOOKUP.

**XLOOKUP Function Syntax**

The XLOOKUP function **looks up a range or an array and then returns the value of the first matching result**. The **Syntax** of the XLOOKUP function is as follows:

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

**Arguments:**

**Lookup_value (required)**: the value you are looking for. It can be in any column of the table_array range.**Lookup_array (required)**: the array or range where you search for the lookup value.**Return_array (required)**: the array or range from where you want to get the value.**If_not_found (optional)**: the value to return when a valid match is not found. You can customize the text in the [if_not_found] to show there is no match.

Otherwise, the return value will be #N/A by default.**Match_mode (optional)**: here you can specify how to match lookup_value against the values in lookup_array.- 0 (default) = Exact match. If no match is found, return #N/A.
- -1 = Exact match. If no match is found, return the next smaller value.
- 1 = Exact match. If no match is found, return the next larger value.
- 2 = Partial match. Use wildcard characters like *, ? and ~ to run a wildcard match.

**Search_mode (optional)**: here you can specify the search order to perform.- 1 (default) = Search the lookup_value from the first item to the last item in the lookup_array.
- -1 = Search the lookup_value from the last item to the first item. It helps when you need to get the last matching result in the lookup_array.
- 2 = Conduct a binary search that requires the lookup_array sorted in ascending order. If not sorted, the return result would be invalid.
- -2 = Carry out a binary search that requires the lookup_array sorted in descending order. If not sorted, the return result would be invalid.

For **detailed information on XLOOKUP function arguments**, please do as follows:

1. Type the **below syntax** into an empty cell, please note you only need to type one side of the bracket.

**=XLOOKUP(**

2. Press **Ctrl+A**, then **a prompt box** pops up which shows **the Function Arguments**. And the other side of the bracket is finished automatically.

3. **Pull down the data panel**, then you can see all **six function arguments** of XLOOKUP.

>>> |

**XLOOKUP Function Examples **

I am sure you have mastered the basic principles of the XLOOKUP function now. Let’s dive right into the **practical examples** of XLOOKUP.

##### Example 1: Exact match

**Perform an exact match with XLOOKUP**

Did you ever get frustrated because you had to specify the exact match mode whenever you use VLOOKUP? Luckily, this trouble no longer exists when you try the amazing XLOOKUP function. **By default, XLOOKUP generates an exact match**.

Now, suppose you have a list of office supplies inventory, and you need to know the unite price of one item, say mouse, please do as follows.

Type the **below formula** into the blank cell F2, and press the **Enter** key to get the result.

**=XLOOKUP(E2,A2:A10,C2:C10)**

Now you know the unit price of Mouse with the advanced XLOOKUP formula. Because the match code has defaulted to an exact match, you don’t need to specify it. So much easier and more efficient than VLOOKUP.

**Just a Few Clicks to Get Exact Match**

Perhaps you are using a lower version of Excel and have no plan to upgrade to Excel 2021 or Microsoft 365 yet. In this case, I will recommend **a handy feature** - **Look for a Value in List formula** of **Kutools for Excel.** With this feature, you can get the result without complicated formulas or access to XLOOKUP.

With our **Excel add-in** installed, please do as follows:

1. Click the cell to put the matched result.

2. Go to the** Kutools** tab, click **Formula Helper**, and then click the **Formula Helper in the drop-down list**.

3. In the **Formulas Helper dialog box**, please configure as follows:

- Select
**Lookup**in the**Formula Type section**; - In the
**Choose a formula section**, select**Look for a value in list**; - In the
**Arguments input section**, please do as follows:- In the
**Table_array box**, select the data range that contains the lookup value and the result value; - In the
**Lookup_value box**, select the cell or range of the value you are searching for. Please note it must be in the first column of the table_array; - In the
**Column box**, select the column you will return the matched value from.

- In the

4. Click the **OK** button to get the result.

##### Example 2. Approximate Match

**Perform an Approximate match with XLOOKUP**

To run an **approximate lookup**, you need to** set the match mode to 1 or -1 in the fifth argument**. **When no exact match is found, it returns the next larger or smaller value**.

In this case, you need to know the tax rates of your staff’s incomes. On the left side of the spreadsheet is the Federal Income Tax Brackets for 2021. How can you get the tax rate of your staff in column E? Don’t worry. Please do as follows:

1. Type the** below formula** into the blank cell E2, and press the **Enter** key to get the result.

Then change the formatting of the returned result as you need.

**=XLOOKUP(D2,B2:B8,A2:A8,,1)**

>>> |

*√ Note: The fourth argument [If_not_found] is optional, so I just omit it.*

2. Now you know the tax rate of cell D2. **To get the rest of the results**, you need to **convert the cell references of the lookup_array and return_array to absolute**.

- Double click cell E2 to show the formula =XLOOKUP(D2,B2:B8,A2:A8,,1);
- Select lookup range B2:B8 in the formula,
**press the F4 key**to get $B$2:$B$8; - Select return range A2:A8 in the formula,
**press the F4 key**to get $A$2:$A$8; - Press the
**Enter**button to get the result of cell E2.

>>> |

3. Then **drag the fill handle down** to get all results.

*√ Note: *

**Pressing the F4 key**on the keyboard allows you to**change the cell reference to an absolute reference**by adding dollar signs before the row and column.**After applying absolute reference**to lookup and return range, we changed**the formula in cell E2**to this version:

**=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)**

- When you
**drag the fill handle down from cell E2**,**the formulas**in each cell of column E are**changed only in the aspect of lookup_value**.

For example, the formula in E13 now is turned into this:

**=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)**

##### Example 3: Wildcard match

**Perform a Wildcard match with XLOOKUP**

Before we look into the **XLOOKUP wildcard match function**, let’s first see **what wildcards are**.

In Microsoft Excel, **wildcards are a special kind of character that can replace any characters**. It is particularly** helpful** when you want to carry out partial match lookups.

There are three types of wildcards: **an asterisk (*)**, **question mark (?)**, and **tilde (~)**.

- Asterisk (*) represents any number of characters in the text;
- Question mark (?) stands for any single character in the text;
- Tilde (~) is used to turn the wildcards (*, ? ~) into literal characters. Place tilde (~) in front of the wildcards to fulfill this function;

**In most cases, when we perform the XLOOKUP wildcard match function, we use the asterisk (*) character.** Now let’s see how the wildcard match works.

Suppose you have a list of the Stock Market Capitalization of the 50 Largest American Companies, and you want to know the market cap of a few companies but the company names are for short, this is the perfect scenario for a wildcard match. Please follow me step by step to do the trick.

*√ Note: To perform a wildcard match, the most important thing is to set the fifth argument [match_mode] to 2.*

1. Type the** below formula** to the blank cell H3, and press the **Enter** key to get the result.

**=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)**

>>> |

2. Now you know the result of cell H3. To get the rest of the results, you need to **make the lookup_array and return_array fixed** by placing the cursor in the array and pressing the F4 key. Then the formula in H3 becomes:

**=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)**

3. **Drag the fill handle down** to get all results.

*√ Note: *

- The lookup_value of the formula in cell H3 is "*"&G3&"*". We
**concatenate the asterisk wildcard (*) with the value G3**using the**ampersand (&)**. - The fourth argument [If_not_found] is optional, so I just omit it.

##### Example 4: Look to the left

**Look to the left using XLOOKUP**

One **disadvantage of VLOOKUP** is that it is **constrained to perform lookups to the right of the lookup column**. If you try to look for values left to the lookup column, you will get the #N/A error. Don’t worry. XLOOKUP is the perfect lookup function to solve this problem.

**XLOOKUP** is designed to lookup values to **the left or the right** of the lookup column. It has no limits and meets Excel users’ needs. In the example below, we will show you the trick.

Suppose you have a list of countries with telephone codes, and you want to look for the country name with a known telephone code.

We need to lookup column C and return the value in column A. Please do as follows:

1. Type the **below formula** into the blank cell G2.

**=XLOOKUP(F2,C2:C11,A2:A11)**

2. Press the **Enter** key to get the result.

*√ Note: The XLOOKUP look to left function can replace Index and Match to look for values to the left.*

**Lookup value from right to left with a few clicks**

For those who don’t want to remember formulas, here, I will recommend** a useful feature** -** Lookup from Right to Left** of **Kutools for Excel.** With this feature, you can perform a lookup from right to the left within a few seconds.

With our **Excel add-in** installed, please do as follows:

1. Go to the **Kutools** tab in Excel, find **Super LOOKUP**, and click **LOOKUP from Right to Left** in the drop-down list.

2. In the **LOOKUP from Right to Left dialog box**, you need to configure as follows:

- In the
**Lookup values and Output range section**, specify**the lookup range**and**output range**; - In the
**Data range section**, input**data range**, then specify**the key column**and**return column**;

3. Click the **OK** button to get the result.

##### Example 5: Vertical or Horizontal lookup

**Perform a Vertical or Horizontal lookup with XLOOKUP**

As Excel users, you may be familiar with VLOOKUP and HLOOKUP functions. **VLOOKUP is to look vertically in a column** and **HLOOKUP is to look horizontally in a row**.

Now **the new XLOOKUP combines them both**, meaning that **you only need to use one syntax to perform vertical lookup or horizontal lookup**. Genius, isn't it?

In the example below, we will illustrate how you use only one XLOOKUP syntax to run lookups vertically or horizontally.

**To perform a vertical lookup**, type the **below formula** in blank cell E2, press the **Enter** key to get the result.

**=XLOOKUP(E1,A2:A13,B2:B13)**

**To perform a horizontal lookup**, type the **below formula** in blank cell P2, press the **Enter** key to get the result.

**=XLOOKUP(P1,B1:M1,B2:M2)**

As you can see, **the syntax is the same**. The **only difference** between the two formulas is that you enter **columns** in vertical lookup while you enter **rows **in horizontal lookup.

##### Example 6: Two-way lookup

**Perform a Two-way lookup with XLOOKUP**

Are you still using **INDEX and MATCH functions** to look up a value in a two-dimensional range? Try **the improved XLOOKUP** to get your job done **more easily**.

XLOOKUP can perform **a double lookup**, finding an **intersection** of two values. By **nesting** one XLOOKUP inside another, the inside XLOOKUP can return an entire row or column, then this returned row or column is entered into the outside XLOOKUP as a return array.

Suppose you have a list of students’ grades with different disciplines, you want to know the grade of Kim’s Chemistry subject.

Let’s see how we use the magical XLOOKUP to do the trick.

- We run the “inner” XLOOKUP to return values of an enter column. XLOOKUP(H2,B1:E1,B2:E10) can get a range of Chemistry grades.
- We nest the “inner” XLOOKUP inside the “outer” XLOOKUP by using “inner” XLOOKUP as a return_array in the complete formula.
- Then here comes the final formula:

**=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))**

- Type the
**formula above**into the blank cell H3, press the**Enter**button to get the result.

Or you can do **the other way around**, use the “inner” XLOOKUP to return values of an entire row, which are all subject grades of Kim. Then use the “outer” XLOOKUP to look for the Chemistry grade among all the subject grades of Kim.

- Type the
**below formula**in the blank cell H4, and press the**Enter**button to get the result.

**=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))**

XLOOKUP’s Two-way lookup function is also the perfect illustration of its vertical and horizontal lookup function. Have a try if you want!

##### Example 7: Customize not found message

**Customize not found message using XLOOKUP**

Just like other lookup functions, when XLOOKUP function **cannot find a match**, the **#N/A error message** will be returned. It might be confusing for some Excel users. But the good news is that **error handling** is available in the** fourth argument of the XLOOKUP function**.

With the **built-in [if_not_found] argument**, you can specify **a custom message to replace the #N/A result**. Type the text you need in the optional fourth argument and enclose the text in **double-quotes (")**.

For example, the city Denver is not found, so XLOOKUP returns the #N/A error message. But after we customize the fourth argument with the text “No Match”, the formula will display the “No Match” text instead of the error message.

Type the **below formula** in the blank cell F3, and press the **Enter** button to get the result.

**=XLOOKUP(E2,A2:A11,C2:C11,"No Match")**

**Customize #N/A error with a handy feature**

To quickly override the #N/A Error with your custom message, **Kutools for Excel** is **a perfect tool** in Excel to help you. With its build-in **Replace 0 or #N/A with Blank or a Specific Value feature**, you can specify the not found message without complicated formulas or access to XLOOKUP.

With our **Excel add-in** installed, please do as follows:

1. Go to the** Kutools** tab in Excel, find **Super LOOKUP**, and click **Replace 0 or #N/A with Blank or a Specific Value** in the drop-down list.

2. In the **Replace 0 or #N/A with Blank or a Specific Value dialog box**, you need to configure as follows:

- In the
**Lookup values and Output range section**, select**the lookup range**and**output range**; - Then
**select the Replace 0 or #N/A with a Specific Value option**,**input the text**you like; - In the
**Data range section**, select the**data range**, then specify the**key column**and**returned column**.

3. Click the **OK** button to get the result. The customized message will be displayed when no match is found.

##### Example 8: Multiple values

**Return multiple values with XLOOKUP**

Another** advantage** of XLOOKUP is its ability to **return multiple values** at the same time for the same match. Enter one formula to get the first result, then other returned values **spill** into the neighboring blank cells automatically.

In the example below, you want to get all the information about student ID “FG9940005”. The trick is to provide a range as the return_array in the formula instead of a single column or row. In this case, the return array range is B2:D9, including three columns.

Type the **below formula** in the blank cell G2, press the **Enter** key to get all the results.

**=XLOOKUP(F2,A2:A9,B2:D9)**

All result cells display the same formula. You can **edit or modify** the formula** in the first cell**, but **in other cells, the formula is not editable**. You can see the formula bar is **grayed out**, meaning no change can be made to it.

All in all, XLOOKUP’s multiple-values function is a **useful improvement** compared to VLOOKUP. You are free from specifying every column number separately for each formula. Thumbs up!

##### Example 9. Multiple Criteria

**Perform multi-criteria lookup using XLOOKUP**

Another **amazing new feature** of XLOOKUP is its ability to **lookup with multiple criteria**. The trick is to **concatenate** lookup values and lookup arrays with the **"&" operator** separately in the formula. Let’s illustrate through the example below.

We need to know the price of the medium blue vase. In this case, three lookup values (criteria) are required to look for a match. Type the **formula below** in the blank cell I2, then press the **Enter** key to get the result.

**=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)**

*√ Note: XLOOKUP can directly process arrays. There is no need to confirm the formula with Control + Shift + Enter.*

**Multi-condition lookup with a quick method**

Is there any **quicker and easier** way to perform a multi-criteria lookup than XLOOKUP in excel? **Kutools for Excel **provides **an amazing feature** - **Multi-condition Lookup**. With this feature, you can run a multiple criteria lookup with just only several clicks!

With our **Excel add-in** installed, please do as follows:

1. Go to the **Kutools** tab in Excel, find **Super LOOKUP**, and click **Multi-condition Lookup** in the drop-down list.

2. In the **Multi-condition Lookup dialog box**, please do as follows:

- In the
**Lookup Values and Output Range section**, select the**lookup value range**and the**output range**; - In the
**Data Range section**, please do the following operations:- Select the
**corresponding key columns**that contain the lookup values one by one by holding the**Ctrl**key in the**Key column box**; - Specify the
**column**which contains the returned values in the**Return column box**.

- Select the

3. Click the **OK** button to get the result.

*√ Note: *

- The Replace #N/A error value with a specified value section is optional in the dialog box, you can specify it or not.
- The number of columns entered in the Key column box must be equal to the number of columns entered in the Lookup Values box, and the order of criteria in both boxes must correspond one to one with each other.

##### Example 10. Find Value with the Last Match

**Get the last matching result with XLOOKUP**

To find the **last matching value** in Excel, set the** sixth argument** in the XLOOKUP function to **search in reverse order**.

**By default, the search mode in XLOOKUP is set to 1**, which is **search from first to last**. But the good thing about **XLOOKUP** is that **the direction of lookup can be changed**. XLOOKUP offers the **optional [search mode] argument** to control the search order. Simply set the search mode in the sixth argument to** -1**, the lookup direction is changed to search from last to first.

Please see the example below. We want to know the last sale of Emma in the database.

Type the **formula below** in the blank cell G2, then press the **Enter** key to get the result.

**=XLOOKUP(F2,B2:B11,D2:D11,,,-1)**

*√ Note: The fourth and fifth arguments are optional and omitted in this case. We only set the optional sixth argument to -1.*

**Easily look up the last matching value with an amazing tool**

In case you have no access to XLOOKUP and also don’t want to remember complicated formulas, you can apply the **Lookup from Bottom to Top feature** of **Kutools for Excel** to get it done **easily**.

With our **Excel add-in** installed, please do as follows:

1. Go to the **Kutools** tab in Excel, find **Super LOOKUP**, and click **Lookup from Bottom to Top** in the drop-down list.

2. In the **LOOKUP from Bottom to Top dialog box**, you need to configure as follows:

- In the
**Lookup values and Output range section**, select the**lookup range**and**output range**; - In the
**Data range section**, select the**data range**, then specify the**key column**and**return column**.

3. Click the **OK** button to get the result.

*√ Note: The Replace #N/A error value with a specified value section is optional in the dialog box, you can specify it or not.*

**Download XLOOKUP Sample File**

** Related articles:**

- How To Use INDEX And MATCH Together In Excel?
- How To Apply Fuzzy Lookup To Find Approximate Match Result In Excel?
- How To Use Two-Way Lookup Formula In Excel?
- How To Vlookup Value With Multiple Criteria In Excel?
- How To Vlookup Values From Right To Left In Excel?

## The Best Office Productivity Tools

### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!