How to Use the NEW & ADVANCED 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 to replace VLOOKUP, HLOOKUP, INDEX+MATCH, and other lookup functions.

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

How to Get XLOOKUP?

Syntax

Examples

• Example 1: Exact match
  • Perform an exact match with XLOOKUP
  • Just a few clicks to get the exact match

• Example 2: Approximate match
  • Perform an Approximate match with XLOOKUP

• Example 3: Wildcard match
  • Perform a Wildcard match with XLOOKUP

• Example 4: Look to the left
  • Look to the left using XLOOKUP
  • Lookup value from right to left with a few clicks

• Example 5: Vertical or Horizontal lookup
  • Perform a Vertical or Horizontal lookup with XLOOKUP

• Example 6: Two-way lookup
  • Perform a Two-way lookup with XLOOKUP

• Example 7: Customize not found message
  • Customize not found message using XLOOKUP
  • Customize #N/A error with a handy feature

• Example 8: Multiple values
  • Return multiple values with XLOOKUP

• Example 9: Multiple criteria
  • Perform multi-criteria lookup using XLOOKUP
  • Multi-condition lookup with a quick method

• Example 10: Find value with the last match
  • Get the last matching result with XLOOKUP
  • Easily look up the last matching value with an amazing tool

Download XLOOKUP Sample File

How to Get XLOOKUP?

Since XLOOKUP function is only available in Excel for Microsoft 365, Excel 2021 and later version, and Excel for the web. If you're using Excel 2019 or earlier, consider upgrading to access XLOOKUP.

Syntax

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

Arguments:

1. Lookup_value (required): the value you are looking for. It can be in any column of the table_array range.
2. Lookup_array (required): the array or range where you search for the lookup value.
3. Return_array (required): the array or range from where you want to get the value.
4. 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.
5. 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.
6. 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 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.

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.

>>>

Examples

I am sure you have mastered the basic principles of XLOOKUP 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 unit 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.

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.

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.

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 the 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 entire 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 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.

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 that 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!

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.

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

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 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.

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

XLOOKUP Examples.xlsx

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?