Office 365 now boasts a powerful successor to the VLOOKUP function – XLOOKUP formula. Think of XLOOKUP as VLOOKUP 2.0. In this article, learn all about the function, syntax, optional parameters and 13 xlookup examples.
What is XLOOKUP?
XLOOKUP is the newest member of Excel lookup function family. You may already know its siblings – VLOOKUP, HLOOKUP, INDEX+MATCH, LOOKUP etc.
XLOOKUP allows us to search for an item in a range (or table) and return matching result. In a way, it is similar to VLOOKUP, but offers so much more.
When using XLOOKUP, you just provide 3 basic parameters (and 3 more optional parameters) and Excel does the rest.
- The value you are looking for
- The list where this value should be found
- The list from which you want the result
- [optional] value if not found
XLOOKUP example:
=XLOOKUP(“Jamie”, Sales[Sales Person], Sales[Net Sales])
Returns [Net Sales] for Jamie if the name exists in [Sales Person] column.
If you compare this with VLOOKUP, then you see that we no longer need to specify column_number or true/false to perform the search.
This also means, unlike VLOOKUP, XLOOKUP can actually look anywhere in the data and find the result, not just on the left.
No more complex INDEX+MATCH formulas or weird VLOOKUP concoctions.
How is XLOOKUP better?
- XLOOKUP makes the most used formula dynamic in Excel straight forward and less error prone. You just write =XLOOKUP(what you want to find, the list, the result list) and boom, you get the answer (or #N/A error if the value is not found)
- Looks up exact match by default: One of the annoyances of VLOOKUP is that you must mention FALSE as last parameter to get correct result. XLOOKUP fixes that by doing exact matches by default. You can use match mode parameter to change the lookup behavior if you want.
- 4th parameter to support value not found scenario In most business situations, we are forced to wrap our lookup formulas with IFERROR or IFNA formulas to suppress errors. XLOOKUP offers 4th parameter (read more about it below) so you can tell what default output you want if your value is not found.
- XLOOKUP offers optional parameters to search for special situations. You can search from top or bottom, you can do wild card searches and faster options to search sorted lists.
- It returns reference as output, not the value. While this may not mean much for normal users, pro Excel user’s eyes light up when they discover a formula that can return refs. That means, you can combine XLOOKUP outputs in innovative ways with other formulas.
- It is so much cooler to type, you just type =XL. I am not sure if this is a happy coincidence, but saying =XL to get this formula is just awesome.
XLOOKUP Syntax
Simple case:
=XLOOKUP(what you want to look, lookup list, result list)
=XLOOKUP("Jackie", sales[Sales Person], sales[Net Sales])
returns Jackie’s [Net Sales] if the name can be found in [Sales Person]
Optional parameters:
By default, you just need 3 parameters for XLOOKUP, as shown above. But you can also use 4th, 5th and 6th parameter to specify how you want the lookup to be done.
4th parameter for XLOOKUP: IF not found (no more IFERROR!!!)
The newly introduced XLOOKUP has an even newer feature. It now supports if not found option. This is the 4th parameter.
For example, use:
=XLOOKUP(“Chandoo”, sales[Sales Person], sales[Net Sales],”Value not found”) to return “Value not found” if the lookup value is not available in the search column – sales[Sales Person].
5th parameter for XLOOKUP: Match mode or type:
Use this to tell Excel how you want your MATCH to happen. The default is 0 (exact match) but you can also use these other options, shown below.
6th parameter for XLOOKUP: Match direction
Try this if you want to search from bottom to top. The default direction is top down (1).
XLOOKUP Introduction & Examples – Video
Here is a video that explains XLOOKUP in no nonsense terms. Watch it below or go to my YouTube channel.
XLOOKUP replaces INDEX+MATCH
Since you can specify lookup column and return column as two parameters, XLOOKUP now replaces INDEX+MATCH combination. I haven’t used INDEX+MATCH for looking up in over a year now. If you want to understand this use case, check out below video or example file.
13 XLOOKUP examples to really understand it
My computer received XLOOKUP update just 90 minutes ago and I have been playing with it ever since. Here are 13 different, fun & creative examples to really understand it. Have a read. If you already have XLOOKUP, feel free to download the sample workbook and have a play.
For all of these examples, we will use the [Sales] table data shown below. XLOOKUP works just as well with cell references too, but I am using structural references for ease of understanding.
XLOOKUP Examples – Table
All the examples are listed in this table. Browse it and feel free to copy the formulas to test.
Note about references in the formula:
- Input or search values are in column H. The value used for searching is shown in first column. I used references rather than hardcoded values to make the formula relateable.
- Search is done against Sales Table as shown above.
| Criteria | Question | Answer | Formula | Notes |
|---|---|---|---|---|
| Jackie | What is the net sales? | 1610 | XLOOKUP(H5,sales[Sales Person],sales[Net Sales]) | Nice and simple. Finds H5 (Jackie) in the sales[Sales Person] list and returns macthing [Net Sales] if found. |
| 2133 | Whose sales are this? | Jamie | XLOOKUP(H6,sales[Net Sales],sales[Sales Person]) | This time, we lookup in the middle but return the name at front. Normally we would use INDEX+MATCH for something like this, but XLOOKUP just kills it. |
| Who has most sales? | Jamie | XLOOKUP(MAX(sales[Net Sales]),sales[Net Sales],sales[Sales Person]) | Of course, we can mix formulas too. MAX() finds the maximum sales and then XLOOKUP does the rest. Try other formulas like MIN(), SMALL(), LARGE() too. | |
| 8 | Who has this many customers? | Joseph | XLOOKUP(H8,sales[No. Customers],sales[Sales Person]) | Another example with find the middle, return from front. |
| 8 | Who is the last person to have this many customers? | Jamie | XLOOKUP(H9,sales[No. Customers],sales[Sales Person],,0,-1) | Now we are talking!!!, you can use the optional parameters for XLOOKUP to specify match type (0 is for exact match) and match direction (-1 is for bottom to top). |
| 1800 | Whose sales are closest to this number, but not more? | Jagjit | XLOOKUP(H10,sales[Net Sales],sales[Sales Person],"value not found",-1) | We can search for a value that is closest but not more by using match type -1. |
| Ju | What is the profit of person whose name begins with this? | 1023 | XLOOKUP(H11&"*",sales[Sales Person],sales[Profit / Loss],,2) | You can do wild card searches too. * for any number of letters and ? for single letter. |
| Who has least sales? | Jimmy | XLOOKUP(0,sales[Net Sales],sales[Sales Person],,1) | Time for a trick!!! When searching fields like [Net Sales] which will usually have just positive values, you can look for 0 with match type as 1 (next highest value). | |
| What is the sales for very last person? | 1415 | XLOOKUP("*",sales[Sales Person],sales[Net Sales],,2,-1) | Another trick! Search for "*" from end to get the last value's matching sales. | |
| Who is the very last person? | Karl | XLOOKUP("*",sales[Sales Person], sales[Sales Person],,2,-1) | Of course, you can search and return from the same column to find out the last person's name. | |
| Net Sales | What is H11 for Johnson? | 1540 | XLOOKUP("Johnson",sales[Sales Person],XLOOKUP(H15,sales[#Headers],sales)) | 2 way lookups by nesting XLOOKUP. Remember, inner XLOOKUP returns a list of [Net Sales] |
| Jamie | What is the Net Sales for second person with this name? | 2724 | XLOOKUP(H16&"2",FILTER(sales[Sales Person],sales[Sales Person]=H16)&SEQUENCE(3),FILTER(sales[Net Sales],sales[Sales Person]=H16)) | You can combine XLOOKUP with other new formulas like FILTER() to create something crazy and fun too. Try it yourself. |
| Chandoo | What is the net sales? | Value not found | XLOOKUP(H17,sales[Sales Person],sales[Net Sales],"Value not found") | And of course, when the data can't be found XLOOKUP simly #N/As. To prevent this, you can use the 4th parameter to specify output value. |
How can I get XLOOKUP?
You need Microsoft Office 365 to use XLOOKUP. You can purchase it from here.
I am using different version of Excel, Can’t get XLOOKUP…
Don’t beat yourself up if you can’t lay your hands on that sweet sweet XLOOKUP yet. Excel already has 100s of powerful functions to perform magic on your data. See below list for alternatives.
| Scenario | ? | XLOOKUP Alternative |
| Regular lookups | Use VLOOKUP or INDEX+MATCH | |
| Lookup in the middle, get value from elsewhere | Use INDEX+MATCH | |
| Get the last item (lookup from last) | Lookup last value trick | |
| Wild card lookups | VLOOKUP does wildcard search too | |
| Nearest value lookups | See this example | |
| 2-way lookups (lookup row & column intersection) | Use INDEX MATCH MATCH | |
| IF not found option | Use IFERROR |
Download XLOOKUP examples workbook
Click here to download the 13 XLOOKUP examples file. Kindly note that it will not work if you do not have XLOOKUP. So upgrade your office first.
Here is another Example workbook, this is used in the video above.
And one more example file, this with INDEX+MATCH replacements.
Got XLOOKUP yet? Share your thoughts
Have you played with XLOOKUP yet? I have been using it for an year now and I am still excited everytime I type it. What about you? Please share your thoughts and creative uses of it in the comments section.
The post What is XLOOKUP? 13 formula examples to really understand it appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.
source https://chandoo.org/wp/xlookup-examples/
0 Comments