TED# 031: A Deep Dive into XLOOKUP - Part 2
Breaking down one of Excel’s most versatile and useful functions.
Don’t forget to download the sample data at the end of the newsletter so you can practice along!
Welcome back! Last week we started our dive into XLOOKUP - one of Excel’s most versatile and useful functions. This week we will continue to look at one of XLOOKUPS lesser known elements. We will also learn how to use XLOOKUP in rows.
Revisiting the syntax
Let’s review the syntax for XLOOKUP quickly:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value: This is the value you want to search for. It can be a single cell OR an array.
lookup_array: Lookup array are the range of cells that will contain the value you want to lookup.
return_array: This range of cells will have the corresponding information that you want to return.
[if_not_found]: An optional argument that specifies what value you want to return if lookup_value is not found in search_range. The default is to return #N/A.
[match_mode]: Determines how Excel should match the lookup_value.
0 or omitted: Exact match.
-1: Exact match or next smaller item if not found.
1: Exact match or next larger item if not found.
2: Wildcard character match
[search_mode]: Determines the direction of the search.
1 or omitted: Search first-to-last (default).
-1: Search last-to-first (useful for retrieving the last occurrence).
2: Binary search (sorted ascending).
-2: Binary search (sorted descending).
Using horizontal mixed with vertical XLOOKUP
What makes XLOOKUP so powerful is that it can lookup information in both the horizontal and vertical directions. Lets take a look at the same example as last week, but instead of the data being presented in columns it is provided in rows. Here it is:
Note: Horizontal data is hard to capture in entirety. Please excuse the data being cut off.
This time the Names appear in row 1, the Email appears in row 2.
I wont go through each step, you can reference last weeks newsletter. However you should see what the new syntax will look like:
Notice what happened here? The lookup_value(s) are listed in a column. The lookup_array & return_array are listed in rows. As you can see below, XLOOKUP handled it easily:
[match_mode]
Match mode is an element of the XLOOKUP function that allows us to utilize approximations. Here are the arguments that you can enter inside the [match_mode] element:
0 or omitted: Exact match.
-1: Exact match or next smaller item if not found.
1: Exact match or next larger item if not found.
2: Wildcard character match
Lets look at an example of when you would use this element. Everyone’s favorite subject, taxes.
We want to figure out the amount of income tax for each person listed. However the taxes are bracketed. So anyone making up to $75,000 pays 5%. Anyone earning up to $500,000 pays 40%.
Step 1: Enter XLOOKUP and the following information for the first 3 elements:
lookup_value: A2:A8
lookup_array: E2:E5
return_array: F2:F5
Step 2: Skip [if_not_found].
Step 3: For match_mode we now can choose from 1 of the 4 options. In this case we will select: 1 - Exact match or next larger item if not found. This tells the function that if it cannot find the exact number go to the next highest value found in the array.
For example, Mayas income is $50,694.00. Since that number is not found in the array the function will simply go to the next larger item which is $75,000 and use that as the lookup value. Here are the results:
As you can see with XLOOKUP you are really starting to see a lot of versatility. Even the possibility of cross referencing a vertical and horizonal lookup with such ease was impossible to imagine before it. There is still a lot to cover. Next week we will jump into two-way lookups as well as the [search_mode] function.
Until then make sure you practice what we have covered today. Here is a link to the sample data so that you can follow along.
When you’re ready, there are a few other ways I can help you master Excel:
Do you have any burning questions about Excel that you want answered? Send your question to ajay@theexceldojo.com. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you found this newsletter informative please share with a friend or two...