TED# 030: A Deep Dive into XLOOKUP - Part 1
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!
Perhaps one of the most popular of all the formulas in Excel is =XLOOKUP. Justifiably so - looking up values from a table is a highly popular Excel practice. Users have been using HLOOKUP, VLOOKUP, INDEX, & MATCH for quite some time now. XLOOKUP is a Dynamic Array Formula - one of Excel’s biggest updates in the past few years. With XLOOKUP you can lookup columns, rows, search left to right or right to left, and much more. It’s versatility opens up a lot of possibilities and will also reduce a lot of headaches.
Breaking down =XLOOKUP()
In order to get the most out of XLOOKUP you need to understand what information it needs in order to work correctly. It has a whopping 6 elements! Understanding each part of the syntax can help you quickly master how to use XLOOKUP step by step. This is because it is relatively intuitive. Here is the full syntax:
=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).
The three portions of the syntax found in the brackets are optional. This makes XLOOKUP very simple and straightforward to use to a beginner.
A simple example
Let’s take a look at a simple application of XLOOKUP. In this case we have a list of names in one column, and an email in other.
In column D we have a list of names and we want to lookup their email addresses and place them in column E.
Step 1: Type in XLOOKUP and select the lookup_value. In this case it will be the contents of cells D2:D6.
Step 2: Next we select the lookup_array, which will be the contents of A2:A12.
Step 3: Now we select the return_array, B2:B12.
Step 4: We enter in a final argument for if_not_found. In this case we will use “Not Listed”. This will change the default if no value is found from #NA to “Not Listed”.
Step 5: Finally we simply hit enter. No double click trick. No boom. The data “spills” into place.
And just like that - XLOOKUP returns the answers we are looking for. It also tells us which one of the name did not appear on the list.
Notice the little shadow box the arrow is pointing at? This indicates data that has been spilled in to place. This is a feature of Dynamic Array Formulas.
I am going to be brutally honest, when I use XLOOKUP I use the 4 elements in the above example 99% of the time. And I only use the 4th because I hate seeing the #NA.
However the 1% of the time where I need the other 2 arguments I am glad they are there. Next week we will dive deeper into more complex applications of XLOOKUP.
In the meantime download the sample data so that you can practice what you’ve learned today!
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.
Want to learn more Excel Shortcuts? I have created something special (and free) for you: The Best Damn Excel Shortcut Sheet
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you found this newsletter informative please share with a friend or two...