This week, let’s try something new. I have a spreadsheet challenge for you. Solve it, post your answers by leaving a comment. No cheating or no AI help on this one. Mandatory Training at Awesome Chocolates You are the head of L&D department at Awesome Chocolates. As part of employee induction, every new hire at […]
This week, let’s try something new. I have a spreadsheet challenge for you. Solve it, post your answers by leaving a comment. No cheating or no AI help on this one.
Mandatory Training at Awesome Chocolates
You are the head of L&D department at Awesome Chocolates. As part of employee induction, every new hire at Awesome Chocolates must complete these 3 trainings:
CM101 – Chocolate Making – 101
CC101 – Cookies vs Chocolates
EAT101 – How to truly savor a chocolate
You were given a spreadsheet with 2 tables of data (download it here).
1) All the new joiners since 1 Jan 2026 (table name: staff)
2) All the training done by your staff since 1 Jan 2026 (table name: trainings)
Based on these two tables, please answer below 3 questions.
Question 1: How many employees have completed all the mandatory trainings?
Find out the total number of employees (started on or after 1 Jan 2026) who completed all 3 mandatory trainings? A mandatory training is considered to be completed only if the employee passes the final assessment.
Question 2: How many employees have completed at least one mandatory training?
Find out the total number of employees (started on or after 1 Jan 2026) who completed at least one mandatory training? A mandatory training is considered to be completed only if the employee passes the final assessment.
Question 3: How many employees have not attempted any of the mandatory trainings? Who are they?
Find out the total number of employees (started on or after 1 Jan 2026) who have not attempted at least one mandatory training? List all such employee ids & names. A mandatory training is attempted if the employee starts the course (irrespective of pass / fail).
Bonus Question: List training completion rates by department
Generate a summary table that shows department name, total head count of new starters, how many have done all the mandatory trainings, percentage of completion (done count / headcount).
Post your answers on the homework page or reply back to me with your answers.
or even link both tables via relationships so you can pivot your way to answers
Don’t struggle, Gain Excel Skills
If you struggle with solving business questions like this using Excel formulas or pivots, my Excel School program is the perfect fit for you.
It is designed to give you confidence and right skills so you can efficiently solve business problems, build proven Excel solutions and be a rock star at work.
I will be including a quick & elegant solution for these questions along with clear explanation in my Excel School program next week. You can access it along with 20+ hours of amazing spreadsheet skills content when you enroll in the program.
Here is a fabulous New Year gift to you. A free 2025 Calendar Excel Template with built-in Activity planner. This is a fully dynamic and 100% customizable Excel calendar for 2025.
Here is a fabulous New Year gift to you. A free 2026 Calendar Excel Template with built-in Activity planner. This is a fully dynamic and 100% customizable Excel calendar for 2026.
See the calendar for entire year in a single view
Dynamic any month calendar with detailed plan view
Beautifully formatted and ready to print 12 month calendar view
Automatic updation of holidays, weekends and activities
Snapshot of upcoming activities
Fully customizable – start on any day, any weekend, custom holidays
Optimized for screen and print outs
Click the below button to download the free 2025 calendar template.
Calendar tab: See the 12-month calendar view + upcoming activities in this page. It also highlights any holidays, weekends and planned activities on the calendar in a different color.
Any month tab: This page lets you see the calendar for any specific month in a detailed view. You can change the month from cell C3 and the calendar updates automatically. The calendar shows date, any activities planned in a neat grid view.
Printable 12 Month Calendar: This tab presents an elegant and ready-to-print 12 month calendar. You can print it or save this as PDF to generate all the 12 pages instantly. The colors and fonts are also fully customizable.
Planner tab: Use this tab to set up your activities. Whatever items you list here will automatically show up on the calendar & any month tabs.
Customizations tab: Do you want to change the way your week begins? Need to add some holidays or change the icons? Use the customizations tab.
How is this calendar made?
The calendar workbook has two main components.
Calendar
Planner
Calendar Generation
To generate the calendar, I am using the dynamic array functionality of Excel 365. We can use the SEQUENCE function to create all the dates in any given year.
For example, =SEQUENCE(365,,DATE(2026,1,1)) generates all the 365 dates in the year 2026.
I then used the same logic to generate monthly calendars for all the 12 months and adjusted them based on the week start option.
Once the monthly calendars are generated, then I highlighted the weekends, holidays and activities using conditional formatting.
Activity Planner
You can set up any number of activities in the planner table. I am then using FILTER function to filter out the activities for a given day and show them next to the calendar date.
Also, if “highlight activities” is enabled, then I am highlighting the calendar cells in a different color.
In the 12-month calendar view, I am showing upcoming 10 activities using FILTER function too.
Interactive any month calendar page:
We use the same logic as above, but limit it to a selected month (with data validation drop-down) to show the calendar for any specific month. Here is the calendar for April 2026.
Know more about these calculations
If you want to learn more about the calculations and set up of this workbook, please refer to these articles + videos.
Let’s try something different. I will share a data analytics challenge here. Post your solutions in the comments. Our first challenge involves Employee Data Analysis. You can score maximum of 35 points. Imagine you have employee data in this “staff” table (in Excel / Power BI / SQL / Python or whatever tool you fancy) […]
Emp ID Name Reports to
TO-0002 Jim Halpert TO-0007
TO-0004 Pam Beesly TO-0015
TO-0007 Michael Scott TO-0067
TO-0009 Erin Hannon TO-0004
TO-0010 Jan Levinson TO-0067
TO-0013 Karen Filippelli TO-0076
TO-0015 Dwight Schrute TO-0007
TO-0018 Andy Bernard TO-0002
TO-0021 Cathy Simms TO-0002
TO-0024 Kevin Malone TO-0035
TO-0028 Creed Bratton TO-0007
TO-0030 Toby Flenderson TO-0067
TO-0031 Kelly Kapoor TO-0028
TO-0033 Robert California TO-0067
TO-0035 Angela Martin TO-0007
TO-0039 Stanley Hudson TO-0007
TO-0043 Ryan Howard TO-0031
TO-0046 Oscar Martinez TO-0035
TO-0050 Meredith Palmer TO-0028
TO-0051 Gabe Lewis TO-0072
TO-0055 Phyllis Vance TO-0030
TO-0059 Roy Anderson TO-0004
TO-0063 Nellie Bertram TO-0030
TO-0066 Darryl Philbin TO-0039
TO-0067 David Wallace TO-0072
TO-0068 Pete Miller TO-0015
TO-0072 Jo Bennet
TO-0076 Todd Parker TO-0002
Emp ID Name Reports to
TO-0002 Jim Halpert TO-0007
TO-0004 Pam Beesly TO-0015
TO-0007 Michael Scott TO-0067
TO-0009 Erin Hannon TO-0004
TO-0010 Jan Levinson TO-0067
TO-0013 Karen Filippelli TO-0076
TO-0015 Dwight Schrute TO-0007
TO-0018 Andy Bernard TO-0002
TO-0021 Cathy Simms TO-0002
TO-0024 Kevin Malone TO-0035
TO-0028 Creed Bratton TO-0007
TO-0030 Toby Flenderson TO-0067
TO-0031 Kelly Kapoor TO-0028
TO-0033 Robert California TO-0067
TO-0035 Angela Martin TO-0007
TO-0039 Stanley Hudson TO-0007
TO-0043 Ryan Howard TO-0031
TO-0046 Oscar Martinez TO-0035
TO-0050 Meredith Palmer TO-0028
TO-0051 Gabe Lewis TO-0072
TO-0055 Phyllis Vance TO-0030
TO-0059 Roy Anderson TO-0004
TO-0063 Nellie Bertram TO-0030
TO-0066 Darryl Philbin TO-0039
TO-0067 David Wallace TO-0072
TO-0068 Pete Miller TO-0015
TO-0072 Jo Bennet
TO-0076 Todd Parker TO-0002
1. How many people are directly reporting to my boss? (5pts)
The first question is simple. For a given employee ID (say TO-0021, Cathy Simms), how many people are directly reporting to their boss (TO-0002, Jim Halpert)? The answer should be 3.
Write the necessary Excel formula / DAX / Power Query / SQL or Python code to find the answer.
2. Who is my boss’s boss? (10pts)
We are going to level up. For a given employee ID (say TO-0021, Cathy Simms), find out their boss’s boss. The answer should be TO-0007, Michael Scott.
3. What is my reporting chain? (Score=20pts)
For a given employee (say TO-0021, Cathy Simms), print their entire reporting chain, delimited by the symbol ->.
Expected Answer is: Cathy->Jim->Michael->David->Jo
Note: You may assume a maximum depth of 7 nodes if that helps.
If you operate a business in New Zealand (NZ) like me, chances are you too need to calculate GST on purchases & sales. Today, let me share the excel formulas needed to calculate GST as per NZ laws. I have also attached a free GST calculator template to help you if you are in a […]
If you operate a business in New Zealand (NZ) like me, chances are you too need to calculate GST on purchases & sales. Today, let me share the excel formulas needed to calculate GST as per NZ laws. I have also attached a free GST calculator template to help you if you are in a hurry.
How to calculate NZ GST using Excel?
Assuming you have the sale price in cell C5, the GST is calculated by the below formula.
=C5 * 0.15
We multiply the “sale” or “service” price by 0.15 (or 15%) as the official GST rate in New Zealand is 15% [ref].
Excel formula for NZ GST from “total” price
Let’s say you want to figure out the GST from total price (GST inclusive price) of something. This is quite common in retail scenarios. You have an item for $140 on the shelves, but you need to figure out what the GST should be on this. In this case, you can use the below Excel formula.
Assuming your “total” price is in cell C15, the GST is calculated with this formula:
=C15 * 15/115
GST Reverse Calculation – What is the “sale price” if I know GST?
Occasionally, we may have the reverse problem. We know how much the GST is, but just need to figure out the total. In this case, you can use the below formula.
Assuming your GST is in cell I5, the sale price can be calculated with below Excel formula
=I5*100/15
and total price can be calculated with this formula
=I5*115/15
The 3 / 23rds and 3/20ths rules
Here is a handy shortcut to quickly figure out the GST from total or sale prices.
3/23rds rule – GST from total
If your total amount is known, just multiply that with 3 and divide by 23 to get the GST.
For example, if your total is $230, then GST would be $30.
=230 x 3 / 23
=690 / 23
=30
3/20ths rule – GST from Sale Price
If you know the “sale” price, just multiply it with 3 and divide by 20 get the GST.
For example, if your sale price is $140, then GST would be $21
=140 x 3 / 20
=420 / 20
=21
GST for hourly rates, services
If you work as a plumber / electrician / some other type of service provider and you charge by hour, then you can use below formulas for calculating GST.
Assuming your hourly rate is in cell I15 and hours worked in cell I16, the GST formula looks like this:
=I15*I16*0.15
FREE NZ GST calculator workbook
I have created a simple, plug-n-play GST calculator workbook for you. Please download it here, enter your price / total / hourly information and the file automatically calculates the GST for you. It also has the GST formulas / patterns that you can apply to your own data.
If you are an NZ business and need spreadsheet help or automation services, please get in touch with me. I am a Wellington based Excel / automation expert and I have been helping clients for the past 15 years in creating simple & easy automation and Excel solutions. Please email me on hello@chandoo.org to discuss more.
Recently, a client shared data with me that is clearly a pivot table and wanted me to make another pivot from it using Excel. This is a common and annoying problem we all face when working with Excel. Today, let me share my approaches for creating a pivot from another pivot report using Excel. Option […]
Recently, a client shared data with me that is clearly a pivot table and wanted me to make another pivot from it using Excel. This is a common and annoying problem we all face when working with Excel. Today, let me share my approaches for creating a pivot from another pivot report using Excel.
Option 1: If you have access to “original” data
Ha, I know, but we can dream eh? So, if you do have access to the original data from which the pivot is generated, just use that data and make the new pivot as you want.
Option 2: Making a Pivot from Another Pivot when you don’t have access to original data
Tbh, this is the real scenario for most of us. We have a pivot and don’t have access to the data that was used to make it. Now we need to make another pivot. In this case, follow the below steps.
Pivot from Another Pivot – FREE Excel Template
I created a free Excel template to guide you thru the process with sample data. Download it here and use the sample data to understand the process better.
Step 1: Select and name your pivot range
Select the entire pivot table (including any headers) in Excel.
Go to the name box (next to formula bar on the left)
Type the name “pivot_range”
Pro tip: If you have multiple pivots, you can use names like “pivot_range1”, “pivot_range2”
See this illustration for the step.
Step 2: Go to Data Ribbon and load up the “pivot” to Power Query
Keep the pivot table selected
Go to Data Ribbon
Click on “From Table/Range” option in the Get & Transform Data area
This will load the Power Query Editor with your Pivot Table Data.
Step 3: Let’s “unpivot” the Pivot Table with Power Query
Now that our “pivot table” is in Power Query, we can “unpivot” it and create a regular table. This can be used to make our new pivot table.
Here is a snapshot of how the Power Query editor looks with the pivot_range data.
[optional step] Promote headers if needed
Depending on how your source Pivot is setup, you may need to adjust the column headings in Power Query. For example, in my case, I need to promote the headers. To do this, click on “Use First Row as Headers” button in the Home ribbon of Power Query editor.
See below illustration.
Step 4: Replace “null” with value from above
In my sample pivot, you can see that Rep name is not printed in all rows, just the first row. This shows up as null in the Power Query editor for rest of the rows. We just need to fill these down based on the top value.
Select the column(s) with this problem
Go to “Transform” ribbon in Power Query Editor
Click on “Fill” and select Down to fill down all the nulls with the value from above
Step 5: Remove rows with “totals” & “sub-totals”
We don’t need totals or sub-totals any more. We will calculate them in the new pivot as needed. For now, let’s remove all the rows and columns that have totals.
Select the first column that has “total” labels
Click on “filter” button
Uncheck any total labels.
Repeat the steps for any other rows that need this clean-up step.
Pro tip: Use Text Filters > Does not contain to filter out all rows with “total” word in them.
Step 6: Remove Grand total / Sub-total columns (if any)
Let’s also remove any “grand total” columns and “sub-total” columns from our pivot report. Right click on the column with totals and select “remove” to take this column out.
Step 7: Unpivot the data
Finally, our pivot report is ready to be unpivoted.
Select the column(s) with row labels. In the above example, I selected “representative” and “day of week” columns
Pro Tip: Hold SHIFT or CTRL to select multiple columns in one go.
Right click on the column headings of either column.
Select “unpivot other columns”
This should reshape the pivoted data to unpivoted format.
See this quick demo (GIF):
Step 8: Rename the new “attribute” & “value” columns
Double click on the newly added “attribute” and “value column headers to rename them to appropriate labels. In my case, I named them – Gender & Calls.
Step 9: Load the data back to Excel so we can make the pivot
Ok. We are done. Just load the data back to Excel. To do this, go to “Home” ribbon and click on “Close & Load” button.
Step 10: Create the Pivot from the loaded data
Once the data is in Excel, just select any cell in the data, go to Insert > Pivot Table (shortcut: ALT N V T) and set up the pivot as per your needs. In my case, I needed the pivot report with number of calls by Day of Week & Representative. So here is how I made it (see the quick video demo).
Things to keep in mind:
The Power Query based approach to create pivot from another pivot is great, but you need to keep a few things in mind.
Doesn’t work for averages: If your original pivot table has “averages” instead of “sums”, the new pivot will not be correct. This is because you will make the mistake of “averaging averages”. This technique works great for sums & counts only. Any other measures like average / median / min /max, you need “actual” data to make the new pivot.
Power Query steps can get complicated: If your original pivot has a very complex, nested layout, then the PQ steps needed to “transform” data can be complex (but not impossible). I suggest learning how to use Power Query to solve such issues. Refer to this article or video to start your PQ journey.
Needs refresh for data changes: If your original Pivot table changes (new values or new rows / columns), you need to update the “pivot_range” named range and refresh the power query data.
To update the named range: Go to Formula ribbon in Excel and click on “Name Manager”. Select the name “pivot_range” and edit it. Adjust the cell references as per your newly updated pivot.
To Refresh Power Query: Right click on the Power Query data you have loaded in Step 9. Select “Refresh’ to update the loaded data with new changes. Now go to the pivot you made (in step 10) and refresh that too (you guessed it right! Right click and Refresh).
What to do if you get an error (in Power Query):
Errors can happen either during the initial process (steps 3 to 9) or when you refresh the power query connection. Solving the error depends on your exact pivot table layout and what changes were made. But here are the most likely reasons for the error.
Column names have changed: You will get error if your columns (in the original pivot) were changed between updates. Adjust the names in the original pivot or go to Power Query editor, locate the step where the error is happening and adjust the names there.
Data type issues: If for some reason, your original pivot’s values are read by Power Query as “text”, it can create issues. Right click on the columns with numbers and explicitly convert them to numbers in PQ.
Layout changes: If your pivot layout changes (say, instead of 2 columns, it now has 3 columns of row labels), then your refresh will fail. You need to select one more column before unpivoting (step 7).
Other issues: Leave a comment with the issue / error you are facing so I can help.
Pivot from Another Pivot – FREE Excel Template
I created a free Excel template to guide you thru the process with sample data. Download it here and understand the process better.
In conclusion:
Power Query in Excel offers an elegant, simple and easy way to deal with the annoying issue of using “pivot tables” as data source. I had plenty of success with this method and I hope will too. If you do have any questions or face issues during the process, leave a comment.
Learn how to use Excel XLOOKUP function with two sheets in this step-by-step tutorial. Why you may want to use XLOOKUP with two sheets? If you have data in two places (sheets or Excel workbooks) like depicted above, you may want to use XLOOKUP to combine data from both places to get the full picture. […]
Learn how to use Excel XLOOKUP function with two sheets in this step-by-step tutorial.
Why you may want to use XLOOKUP with two sheets?
If you have data in two places (sheets or Excel workbooks) like depicted above, you may want to use XLOOKUP to combine data from both places to get the full picture. For example,
Student list in sheet1, course price list in sheet2, you want to know the price of courses against student names in sheet1
Invoice list in sheet1, payment list in sheet 2, you need to know which invoices are paid up (reconciliation)
Equipment list in sheet1, inspection details in sheet2, you want to know when the last inspection date is for each equipment
What you need?
You need two sheets of data.
or if data is in two separate Excel files, then open both files.
XLOOKUP with two sheets (step-by-step instructions)
Step 1: Identify common column between both sheets
For example, in the above scenario, “Fee” is the common column between sheet 1 (student list) and sheet 2 (course list).
What if I have more than 1 common column?
I will explain the process for modifying XLOOKUP to work with multiple columns further down the page. Read on.
Step 2: Write the XLOOKUP formula
Go to the sheet where you want to get the data from “other” sheet and write the XLOOKUP function using the pattern below.
=XLOOKUP(
all cells in first sheet,
common column in second sheet,
column you want in second sheet,
optional output for missing values
)
for example, in our students & fees case, we will use the below XLOOKUP function.
=XLOOKUP(C4:C43,
Courses!B4:B15,
Courses!D4:D15
)
As demonstrated above, xlookup can automatically spill values for all the rows based on the common column you have specified. No need to individually write or drag the formulas. You also don’t need to “lock” your references with this style of formulas. Learn more about the spill functionality and dynamic array behavior of Excel here.
Using Tables? Don’t select the full column in sheet 1
If you are using Excel Tables for your data, just select the current cell in first row but select common column and column you want in sheet 2. Excel will automatically fill the formula down for you.
For example, the same formula with tables could look like this:
We start the lookup with 1. I will explain what this is in a second.
The lookup array has multiple parts, one per common column. As we have two columns to match (course code and student type), we have two parts here.
Part1: (‘Courses NEW’!$B$4:$B$27=Students!C4) checks which courses in column B of sheet 2 (course code column) match with the course of current student record (column C of sheet 1)
Part 2: (‘Courses NEW’!$C$4:$C$27=Students!D4) does the same, but for student type
Part 1 * Part 2: when we multiply both of these checks, we end up with an array of 0s and 1s. for example, it will look like this: {0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
The return array is just the fee column of sheet 2
As we are looking for 1, xlookup matches the 1 in Part 1*Part 2 and returns the corresponding fee.
Generic Formula Pattern for any number of common columns with XLOOKUP
Use this pattern and adjust everything as per your data to match any number of common columns
=XLOOKUP(
1,
(COLUMN 1 in second sheet = value 1 first sheet) *
(COLUMN 2 in second sheet = value 2 first sheet) *
(COLUMN 3 in second sheet = value 3 first sheet) *
(COLUMN 4 in second sheet = value 4 first sheet),
COLUMN YOU WANT TO GET IN SECOND SHEET,
OPTIONAL value for missing cases
)
What if I have data in two separate workbooks (Excel files) instead of sheets
The process is exactly same as two sheets. You just need to keep both files OPEN for the XLOOKUP to work. If you close the second file (one with fees in this example), the formula in first workbook works as long as you don’t touch it or recalculate the workbook (F9). At that point it will throw an error and ask you to open the file.
Alternatives to XLOOKUP for combining data from two places
While xlookup is great, you can also use below alternatives to get data from another place.
Power Pivot to combine data from tables to make a single pivot: You don’t always have to combine data. You can keep things where they are and join tables via common column just like databases with Excel’s Power Pivot feature. This lets you calculate total fees or averages using pivot tables. Refer to this page for an introduction on how to use data model and power pivot feature of Excel.
My preferences:
For simple scenarios and quick analysis, I prefer using XLOOKUP or VLOOKUP to quickly combine data like this.
But if the data is coming from two separate files (workbooks or even sharepoint lists etc.), then I use Power Query. It gives me more flexibility and choices. Refer to my Power Query tutorial page for more spicy examples on what this powerful feature can do for you.
Bonus: XLOOKUP with two sheets: Sample workbook
If you need a hand with the formulas explained above, download my free XLOOKUP two sheets template and refer to the formulas in columns E & F. Let me know if you have any questions by leaving a comment.
Related Resources:
To learn more about the important Excel functions and concepts, refer to below articles & videos:
Think of XLOOKUP as an improved version of VLOOKUP. In this article, learn all about the XLOOKUP function, it's syntax, parameters with real-world xlookup examples.
In this article, learn all about the XLOOKUP Excel function, it’s syntax, parameters and how to use it with real-world xlookup examples.
What is XLOOKUP?
Using XLOOKUP, we can search for an item in a list using the lookup value and return a matching item. For example, you can lookup for salesperson “Jackie” and return their sales amount from the data below using the XLOOKUP function, as depicted above.
It is the newest member of Excel’s lookup function family. You may already know the other members of this group – VLOOKUP, LOOKUP, HLOOKUP, INDEX+MATCH.
How to use XLOOKUP – step by step instructions
Let’s say you have data for salespeople like above and you want to find the Net Sales for “Jackie”. Follow below steps to create the XLOOKUP function in Excel.
Write =XLOOKUP( in a cell
For the lookup_value, Type the name of the salesperson in double quotes (ex: “Jackie”)
Tip: If you have the name of the person in a cell (like G4), you can point to the cell instead of typing the name
Now for the lookup_array, select the names column of your data.
and for the return_array, select the net sales column of your data.
Close the brackets and hit enter.
Congratulations, you’ve just created your first XLOOKUP formula in Excel.
Here is how the formula and result look in my data:
What if the lookup value is not in the lookup array?
One of the most common scenarios of lookups in Excel is not finding the value you are looking for. Imagine, you are looking for the salesperson “Chandoo”, but he doesn’t exist in the dataset. In this case, XLOOKUP will return #N/A error.
But we can use the 4th parameter of XLOOKUP – if_not_found to set an optional value to display when there is an error.
Here is an example formula with that:
=XLOOKUP("Chandoo", B4:B21, D4:D21, "Not found")
How to lookup in the middle with XLOOKUP (INDEX MATCH replacement)?
Let’s say you want to lookup the sales amount of $726 and return the name of the person (in this case, Jessy). Previously, you needed to use the INDEX+MATCH combination for this. But XLOOKUP let’s us specify any range or column for lookup_array portion. so, no more INDEX+MATCH… Yay!
To perform the “net sales” lookup and return the name of the person, we can use the below formula:
=XLOOKUP(G4, G4:G21, G4:G21, "Not found")
The above XLOOKUP example assumes that G4 contains the net sales value you want to lookup.
How to lookup higher than / lower than using XLOOKUP? (approximate match)
Say, you want to find out the closest person with the net sales of $1300. In this case, we don’t have anyone with that value in the data. You can still use XLOOKUP to get the approximate matching value, either next higher or lower in the data.
For this we can use the 5th parameter of the XLOOKUP function – match mode.
There are 4 match modes in XLOOKUP.
0 or Exact match. This is the default value for XLOOKUP.
1 or next larger match. This looks up for the exact or next highest value in the lookup array.
-1 or next smaller match. This looks up for the exact or next lowest value in the lookup array.
2 or wildcard match. This looks up based on a pattern you have mentioned in the lookup value. More on this further down in the article.
To get the next highest matching value:
=XLOOKUP(G4,D4:D21,B4:B21,,1)
Note: 1 refers to next higher value for match mode.
The above formula returns “Jonathan” in the sample data, as he has the next highest amount – $1316.
To get the next lowest matching value:
=XLOOKUP(G4,D4:D21,B4:B21,,-1)
Note: 11 refers to next lower value for match mode.
This formula returns “John” as he has the next lower value – $1088.
Getting Partial Matches with XLOOKUP (Wildcard / Pattern Matching)
XLOOKUP also allows for a powerful and elegant pattern matching in your data. Let’s say you want to find the net sales for the person whose name begins with the letters Jam. In this case, we can use the match mode 2 (wild card) along with the wildcard operators * (asterisk) and ? (question mark) to create our XLOOKUP.
To find the net sales of the person whose name begins with Jam,
Use the formula =XLOOKUP(H4&”*”,B4:B21,D4:D21,”No such person”,2)
Here H4 contains the first few letters of the name, i.e. Jam
The lookup value is H4 & “*”. This tells XLOOKUP that we want the name to begin with the value of H4 (Jam) and then there can be any number of characters.
Rest of the XLOOKUP parameters are as per usual.
Don’t forget the match mode operator as 2. We need this for Wild card – pattern matching.
Refer to above illustration for more on the pattern matching xlookup.
Additional Pattern Matching Tricks with XLOOKUP
Refer to below handy table for some extra tips on using the partial matching feature of XLOOKUP.
SituationFormulaExplanationSample ResultName ends with ved=XLOOKUP(“*ved”, B4:B21,D4:D21,”No such person”,2)To find a word ending with, we use * (asterisk) at the beginning of the patternJaved, $2277Name contains ack=XLOOKUP(“*ack*”, B4:B21,D4:D21,”No such person”,2)We can use two * symbols – one at the beginning of the pattern and one at the end. Jackie, $1610Name has 5 letters and begins with Je=XLOOKUP(“Je???”,B4:B21,D4:D21,,2)In this case, we can use the ? (question mark) symbol to indicate that we can have any one character. So the pattern is Je???Jessy, $726Name begins with J and ends with d=XLOOKUP(“J*d”, B4:B21, D4:D21,”No such person”, 2)J*d means the lookup value begins with J, contains any number of letters and ends with dJaved, $2277
What if there are two matching values?
If you have more than one matching item for the lookup value in your data, XLOOKUP, just like all other lookup functions in Excel, will always return the first matching item’s corresponding value.
As you can see in the above illustration, we have two sales persons with the name Johnson in our data.
When using =XLOOKUP(“Johnson”, B4:B21, D4:D21) we are going to get the net sales of the first Johnson ie $1540.
But what if I need to get the second or third or all the matching values?
In this case, you can use the new FILTER() function in Excel to get the second or all matching items.
Horizontal Lookup with XLOOKUP (HLOOKUP replacement):
XLOOKUP works just as good with horizontal data too, thus replacing any need for HLOOKUP function.
In the above example, I have monthly budget table and I want to lookup the budget value for April 2024.
We can use this XLOOKUP formula to do just that.
=XLOOKUP(C10,C3:N3,C4:N4)
Returning entire row of information with XLOOKUP:
Another powerful feature of XLOOKUP is that it can return multiple values all corresponding to the same lookup value. For example, I want to see the budget, actual and balance information for the month of April 2024, from my budget spreadsheet below. We can use XLOOKUP for that easily.
=XLOOKUP(C10,C3:N3,C4:N6)
In the above formula, by using multiple rows (C4:N6) as the return array, we can return all corresponding values for the lookup value in C10 – ie April 2024. Excel will automatically spill these values into separate cells on the worksheet.
XLOOKUP – things to keep in mind:
When using XLOOKUP, you must keep these points in mind:
XLOOKUP needs Excel 365 or Excel on the web or Excel 2021: This function is not available in all the versions of Excel. So check your version of Excel before you start using xlookup. If you notice #NAME errors when working with XLOOKUP, that means your version of Excel does not support this function. Instead, use INDEX+MATCH formula.
Use the If not found option to fix errors: To avoid any lookup errors (#N/As), use the fourth argument of the XLOOKUP function – if_not_found.
Lock cell references when creating lookups: If you are writing multiple XLOOKUPs in a range, don’t forget to lock your cell references to absolute mode (change B4:B21 to $B$4:$B$21) so that when you drag or fill the formula down, your lookup and return array ranges don’t change. This is a common mistake and you will end up with wrong results. (absolute vs. relative references)
Or better yet, convert your lookup data to a table: A simple fix to the relative reference issue is to use tables on your data. This way, you can write simple XLOOKUP formulas like this: =XLOOKUP(“Jackie”,sales[Sales Person],sales[Net Sales])
In case of multiple matches, XLOOKUP always returns the first (or last matching item, if you used search mode option) value. This is why it is important to also learn how to use the FILTER function in Excel.
How is XLOOKUP better?
XLOOKUP makes the most used formula 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 wildcard 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. For example: XLOOKUP to create dependant drop down in Excel.
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)
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: Search mode
Try this if you want to search from bottom to top. The default direction is top down (1).
XLOOKUP with two sheets
If you have data in two seperate sheets (or files), you can use XLOOKUP to quickly combine the data and get what you want.
When we nest XLOOKUP formulas, we can also perform more complex lookups like 2-WAY lookups or search across different worksheets. I discuss some of these advanced scenarios in a recent video on my YouTube channel. Please watch it here.
The only downside of XLOOKUP is the compatibility. It doesn’t work in all versions of Excel. For this reason, I still think there is value in learning how to use VLOOKUP and INDEX MATCH functions.
Do let me know what you think about XLOOKUP and if you have any questions about it using the comments section.
Learn more about Data Analysis with Excel:
This post is part of my data analysis with Excel series. Please learn other topics too and improve your data skills with Excel.
