Excel 365 has X-cellent upgrade over VLOOKUP

• lookup_value is the value we want Excel to search for.
• lookup_array is where we want Excel to search for the lookup value.
• return_array is where the data exists that we want Excel to return.
• [if_not_found] is optional and allows you to specify a return value if the lookup value is not found in the lookup array. Otherwise, an #N/A error will be displayed.
• [match_mode] is optional and allows you to choose how Excel will match the lookup value in the lookup_array. The default option is to perform an exact match only and will be used if you do not select anything for [match_mode]. This is likely the type of match you would need most often. The other options include:
  • Exact match. If there isn’t one, use the next smaller value (-1).
  • Exact match. If there isn’t one, use the next larger value (1).
  • Wildcard match (2).
• [search_mode] is optional and allows you to choose how Excel will search for the lookup value in the lookup_array. The default option is to perform a search starting with the first item in the lookup_array and will be used if you do not choose anything for search_mode. You will need to input a value for the other options, which include (value in parentheses):
  • Perform a search starting with the last item in the lookup_array (-1).
  • Perform a binary search in ascending order (2).
  • Perform a binary search in descending order (-2).

Don’t worry if you don’t understand those last two search options — it is unlikely many of you would ever need to use those.

Let’s go through some examples. For the first example, we want Excel to look up any product ID entered into cell G10, find it in the set of product information in columns A through E, and return the associated product name. (See the screenshot below.)

The formula is created in cell H10. In this case, we need to define only the first three arguments: lookup_value, lookup_array, and return_array. The lookup_value is the product ID that is entered into cell G10 (“1” in this example). The lookup_array is the column in the set of data where we want Excel to find the lookup value (A2:A28). The return_array is the column(s) that contain the information we want to extract, which in this case is the product name (B2:B28). Therefore, for this example, the formula in cell H10 is =XLOOKUP(G10,A2:A28,B2:B28).

For the second example, we want Excel to do the same thing that we did in the first example, except this time the lookup_array is not the first column of the product information. You would not have been able to do this using VLOOKUP because the lookup_array is not the first column of the data. With XLOOKUP, this limitation no longer exists.

This formula will look like the formula for the first example except the lookup_array is now B2:B28 and the return_array is now A2:A28. This is because those two columns were swapped in the dataset, as shown in the screenshot below. The formula for this example is =XLOOKUP(G10,B2:B28,A2:A28).

For the third example, we want Excel to look up any product ID entered into cell G10, find it in the set of product information in columns A through E, and return the multiple associated product information. A fantastic feature of XLOOKUP is that if the return_array defines multiple rows or columns, the formula will “spill” the results of the formula into multiple columns or rows.

This formula will look like the formula for the first example except that the return_array is now B2:E28, not just B2:B28. (See the screenshot below.) This is because we now want to extract the product information in not only column B, but columns B through column E. And once I enter this formula in cell H10, it will “spill” over into cells I10 through K10. The formula for this example is =XLOOKUP(G10,A2:A28,B2:E28).

For the fourth example, we want Excel to do the same thing that it did in the first example, except this time we want Excel to display “No Product Available” if the product ID entered into cell G10 is not a valid product ID.

This formula will look like the formula for the first example except that we will include a fourth argument. The first three arguments are identical to what we did with the first example, and we will now add the text “No Product Available” for the [if_not_found] argument. Now, when a product ID is entered in cell G10 that does not exist in the dataset, the text “No Product Available” will be displayed. Otherwise, the related product name will be displayed. (See the screenshot below.) The formula for this example is =XLOOKUP(G10,A2:A28,B2:B28,”No Product Available”).

For the fifth example, we want Excel to look up each employee’s salary from column C and determine which tax bracket it falls into based on the information in columns E and F. Then we want Excel to populate column B with the appropriate tax rate. (See the screenshot below.)

This formula will use the first, second, third, and fifth arguments. The formula is created in cell B2. The lookup_value is the employee’s salary that is entered into cell C2. The lookup_array is the column in the set of data that contains the lookup value (E3:E12). The return_array is the column(s) that contain the information we want extracted (F3:F12). I chose not to include anything for [if_not_found] (the fourth argument). For this example, I do not want to use the default of an exact match for [match_mode] (the fifth argument) since Excel needs to look up values within ranges. Therefore, I will choose the option for Excel to look for an exact match and, if one is not found, to match to the next smallest tax bracket. For example, if an employee’s salary were between $20,000 and $24,999, a 2% tax rate would be returned. This fifth argument is inputted as -1. We also want to copy the formula created in cell B2 down to use for all employees. However, we do not want our defined ranges to change as we copy the formulas down. Therefore, we will include absolute references ($) in front of the columns and rows defining those ranges. For this example, the formula in cell C2 is =XLOOKUP(C2,$E$3:$E$12,$F$3:$F$12,,-1).

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

Q. I’ve used VLOOKUP extensively over the years, but I’ve heard that Excel had added a new function that’s better and easier to use. Is that true?

A. Yes, XLOOKUP is a new function for Excel 365 users that essentially replaces VLOOKUP and HLOOKUP. XLOOKUP fulfills the primary goals of the older functions but is more versatile with fewer limitations.

With XLOOKUP, you can extract specific things from an Excel worksheet or workbook. This is extremely valuable when dealing with large datasets. For example, you can extract product information about a specific product from a long list. You can also instruct Excel to display a particular value or message if the user requests information that does not exist in the data.

I have created a workbook with examples of using XLOOKUP. You can access the workbook here and watch the accompanying video at the bottom of this page.

Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

XLOOKUP is one of the new Dynamic Arrays functions Microsoft has added to Excel in Office 365. That means you can use XLOOKUP to look up and extract information from within a range.

The XLOOKUP function contains six arguments with the following syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],[search_mode]). The first three arguments are required, and the last three arguments are optional. This may seem like a lot, but you will likely use only the first three arguments most of the time. The arguments are defined as follows:

• lookup_value is the value we want Excel to search for.
• lookup_array is where we want Excel to search for the lookup value.
• return_array is where the data exists that we want Excel to return.
• [if_not_found] is optional and allows you to specify a return value if the lookup value is not found in the lookup array. Otherwise, an #N/A error will be displayed.
• [match_mode] is optional and allows you to choose how Excel will match the lookup value in the lookup_array. The default option is to perform an exact match only and will be used if you do not select anything for [match_mode]. This is likely the type of match you would need most often. The other options include:
  • Exact match. If there isn’t one, use the next smaller value (-1).
  • Exact match. If there isn’t one, use the next larger value (1).
  • Wildcard match (2).
• [search_mode] is optional and allows you to choose how Excel will search for the lookup value in the lookup_array. The default option is to perform a search starting with the first item in the lookup_array and will be used if you do not choose anything for search_mode. You will need to input a value for the other options, which include (value in parentheses):
  • Perform a search starting with the last item in the lookup_array (-1).
  • Perform a binary search in ascending order (2).
  • Perform a binary search in descending order (-2).

Don’t worry if you don’t understand those last two search options — it is unlikely many of you would ever need to use those.

Let’s go through some examples. For the first example, we want Excel to look up any product ID entered into cell G10, find it in the set of product information in columns A through E, and return the associated product name. (See the screenshot below.)

The formula is created in cell H10. In this case, we need to define only the first three arguments: lookup_value, lookup_array, and return_array. The lookup_value is the product ID that is entered into cell G10 (“1” in this example). The lookup_array is the column in the set of data where we want Excel to find the lookup value (A2:A28). The return_array is the column(s) that contain the information we want to extract, which in this case is the product name (B2:B28). Therefore, for this example, the formula in cell H10 is =XLOOKUP(G10,A2:A28,B2:B28).

For the second example, we want Excel to do the same thing that we did in the first example, except this time the lookup_array is not the first column of the product information. You would not have been able to do this using VLOOKUP because the lookup_array is not the first column of the data. With XLOOKUP, this limitation no longer exists.

This formula will look like the formula for the first example except the lookup_array is now B2:B28 and the return_array is now A2:A28. This is because those two columns were swapped in the dataset, as shown in the screenshot below. The formula for this example is =XLOOKUP(G10,B2:B28,A2:A28).

For the third example, we want Excel to look up any product ID entered into cell G10, find it in the set of product information in columns A through E, and return the multiple associated product information. A fantastic feature of XLOOKUP is that if the return_array defines multiple rows or columns, the formula will “spill” the results of the formula into multiple columns or rows.

This formula will look like the formula for the first example except that the return_array is now B2:E28, not just B2:B28. (See the screenshot below.) This is because we now want to extract the product information in not only column B, but columns B through column E. And once I enter this formula in cell H10, it will “spill” over into cells I10 through K10. The formula for this example is =XLOOKUP(G10,A2:A28,B2:E28).

For the fourth example, we want Excel to do the same thing that it did in the first example, except this time we want Excel to display “No Product Available” if the product ID entered into cell G10 is not a valid product ID.

This formula will look like the formula for the first example except that we will include a fourth argument. The first three arguments are identical to what we did with the first example, and we will now add the text “No Product Available” for the [if_not_found] argument. Now, when a product ID is entered in cell G10 that does not exist in the dataset, the text “No Product Available” will be displayed. Otherwise, the related product name will be displayed. (See the screenshot below.) The formula for this example is =XLOOKUP(G10,A2:A28,B2:B28,”No Product Available”).

For the fifth example, we want Excel to look up each employee’s salary from column C and determine which tax bracket it falls into based on the information in columns E and F. Then we want Excel to populate column B with the appropriate tax rate. (See the screenshot below.)

This formula will use the first, second, third, and fifth arguments. The formula is created in cell B2. The lookup_value is the employee’s salary that is entered into cell C2. The lookup_array is the column in the set of data that contains the lookup value (E3:E12). The return_array is the column(s) that contain the information we want extracted (F3:F12). I chose not to include anything for [if_not_found] (the fourth argument). For this example, I do not want to use the default of an exact match for [match_mode] (the fifth argument) since Excel needs to look up values within ranges. Therefore, I will choose the option for Excel to look for an exact match and, if one is not found, to match to the next smallest tax bracket. For example, if an employee’s salary were between $20,000 and $24,999, a 2% tax rate would be returned. This fifth argument is inputted as -1. We also want to copy the formula created in cell B2 down to use for all employees. However, we do not want our defined ranges to change as we copy the formulas down. Therefore, we will include absolute references ($) in front of the columns and rows defining those ranges. For this example, the formula in cell C2 is =XLOOKUP(C2,$E$3:$E$12,$F$3:$F$12,,-1).

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.