We can calculate any Finance & Accounting KPI values using...

Read MoreThe post Announcing Power BI Dashboard Contest (win $500 prizes!) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>- Participants must use Power BI (any recent version) to create the dashboard.
- The dashboard should be built with native visuals only. No add-ons or marketplace visuals or Python/R visuals.
- The dashboard should contain a readme / help tab to explain the features and methodology.
- Your dashboard should feature the Awesome Chocolates logo, contest title on it. These assets are included in the dataset file below.
- Limit your dashboard to one Power BI Page (readme / help goes on a separate page)
- You are free to combine the business data of Awesome Chocolates with any publicly available datasets. Credit any sources in the readme / help tab.
- The participant agrees to allow indefinite copyright and sharing of the workbooks (and any extra datasets) with chandoo.org for further publication, remixing, and usage.
- Your dashboard should be submitted by
**30th of April 2024**using the upload instructions (to be added later). - Participants agree to have their name and any social media handles listed on the contest website.
- Only one entry per participant. But your dashboard PBIX may contain more than one page with different versions of the dashboard.

I have set up an exclusive community on Microsoft Teams to run & manage this contest. To participate, please follow the below-instructions.

- You need a personal email address (such as @gmail.com or @hotmail.com) or your phone number.
**Go to my Dashboard Champions Circle on Microsoft Teams**- Use your personal email address or phone number to register. You will need to switch to “personal” mode of teams if prompted.
- Join the community and find the dataset & dashboard purpose information inside.
- Start building your dashboard.
- Submit your entry by 30th April using the instructions in the community.

Please download the Awesome Chocolates Dataset using the link inside the teams community.

The purpose of the dashboard is to inform the CEO of Awesome Chocolates about the performance of our business in the last 13 months. The dataset contains daily shipment data from 2023 February to 2024 February. Here are a few things our CEO is interested in, but she likes surprises too. So get creative.

- How sales, units, shipments, profit, profit%, low-box shipments (shipments with box count under 50) trends are looking
- A detailed performance understanding at product or salesperson level
- Interesting patterns in product / geography data
- Ability to drill down to details if needed.

You are encouraged to use various powerful interactive features of Power BI like tooltips, bookmarks, DAX, conditional formatting, pictures and anything else you might fancy. Again, get creative and showcase what you can build, but stick to one standard sized Power BI page.

You should submit your Power BI dashboard by 30th of April, 2024 (end of day, pacific time).

Remember, only one dashboard per person.

Please submit your dashboard using the instructions inside **my exclusive teams community.**

For more information and guidelines, visit the Awesome Dashboard community page.

The total prize money for this competition is $500.

- First prize: $250 Amazon Gift Card
- Second prize: $150 Amazon Gift Card
- Third prize: $100 Amazon Gift Card
- Don’t forget the bragging rights!

A panel of Power BI experts and dashboard pros will select the winners. I will share the details later on the teams community.

All the best

*Note: This competition is powered by Microsoft Teams*

The post Announcing Power BI Dashboard Contest (win $500 prizes!) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>To calculate compound interest in Excel,

on principal amount P

at the rate of interest R

for the number of years N

and compounded T times per year

we can use the formula = P*(1+R/T)^(N*T)

In this article, understand how to calculate various kinds of compound interest values using Excel formulas.

The post Compound Interest Formula in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Compound Interest Formula in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post How to convert test scores to letter grades in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>In your workbook, set up a mapping (or lookup) table like this to map out each of the letter grades to the test score boundary.

- When setting up the mapping table, make sure to start from lower score to higher score (for ex: 0 to 100)
- For each grade, just specify the lower boundary value. So for example, Grade F begins from 0, Grade B- begins from 65

For this you need all the test scores for your students. Let’s say you have the test scores in column C, from cell C4. In an adjacent column, you can calculate the letter grade using the below LOOKUP formula.

**=LOOKUP(C4,$G$6:$G$16,$F$6:$F$16)**

In this formula:

- First value (C4) refers to the score for which you need the letter grade
- Second value ($G$6:$G16) is the “scores from” column of your mapping table set up in Step 1.
- Third value ($F$6:$F$16) is the “letter grade” column of your mapping table.

Once you have a result for the first test score, drag the formula down to see letter grades for all students.

Sometimes you may want to calculate the letter grade from the percentile of the test score. This sort of thing is also called relative grading (RG). To do this, we can use the PERCENTILERANK functions of Excel.

Here is a 3 Step process for that:

In your worksheet, set up a mapping table for letter grades like this:

Let’s say you have test scores in column C, in the range C4:C43.

In column D, write the formula =PERCENTRANK.INC($C$4:$C$43,C4) to calculate the percentile of test score in first cell against all scores. The result of this would be a percentile from 0% to 100% (both inclusive).

When you get the result for first cell, drag the formula down to fill up the rest.

**Tip: **If you want to calculate the percentile by excluding 0th and 100th percentiles, use the PERCENTILE.EXC function instead.

For this, we can use the LOOKUP function again. In column E use the below function:

**=LOOKUP(D4,$H$6:$H$16,$G$6:$G$16)**

In the above formula:

- First value (D4) refers to the percentile we calculated in step 2.
- Second value ($H$6:$H16) is the “percentile from” column of your mapping table set up in Step 1.
- Third value ($G$6:$G$16) is the “letter grade” column of your mapping table.

I made a quick Excel template to calculate letter grades from your test / exam marks. Just plug-in your values and see the results instantly. **Download the template here.**

That is right. All these formulas will work exactly same with Google sheets too. Here is a Google Sheets template if you need some help.

Do take these cautions when calculating alphabetic letter grades from your exam marks.

**Mapping table setup:**your letter grade mapping table needs to be from lowest marks / scores to highest. Just specify the lower boundary for each letter grade.- For example, if grade F is from exam score 0 to 35, then write 0.
- If grade B+ is from 80 to 85, then write 80

**Clean up your data:**If your test score data has missing values (for example, absent or hyphens) then the LOOKUP formula will give #N/A error. So clean up your data before you apply the LOOKUP function.

Now that you have calculated the letter grades, you may want to see the distribution of your student grades or understand which students are failing and need help. Use below Excel concepts & resources to do that.

- Understand the distribution of your data in Excel
- Create a histogram in Excel
- Using conditional formatting in Excel to highlight top 10 values
- How to use Excel Pivot Tables to understand and explore your data

The post How to convert test scores to letter grades in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post How to calculate the Gender Pay Gap using Excel Formulas? (Free Calculator Template) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>* According to NZ Government, *

Gender pay gaps are differences in pay for groups of women and men, usually based on the median or mean pay that men and women receive.

Source: Statistics New Zealand

Assuming you have average salary of men & women in two cells C3 & C4, we can calculate Gender Pay Gap using the below formulas:

**Gender Pay Gap in $s:**

**=C3 - C4**

Generalized formula = average of male salary - average of female salary

**Percentage Gender Pay Gap:**

**=(C3-C4)/C3**

Generalized formula = (average of male salary - average of female salary) / average of male salary

Excel is a great option for identifying and reporting gender pay gap issues when you have full employee data. Let’s say you have the staff data in an Excel table as shown above.

In this case, we can use below formulas to calculate the Gender Pay Gap:

Create a 3-column table in Excel with the staff ID, gender & annualized full-time salary. (Related: Learn how to create a table in Excel)

Name your table as “staff” using the Table Design ribbon in Excel.

You can use AVERAGEIFS function in Excel to calculate the male & female specific average salary.

The formula for male average looks like this:

**=AVERAGEIFS(staff[Annualized Full-time Salary],staff[Gender], "Male")**

And the formula for female average looks similar.

The formulas for this are explained above. They are:

GPG in $s: =Average Male Salary – Average Female Salary

GPG in %: =(Average Male Salary – Average Female Salary) / Average Male Salary

Format the GPG $ and Salary calculations in your currency formatting (Ctrl Shift 4)

Format the GPG % in Percentage formatting using Excel format cells option (CTRL Shift 5)

Please refer to below illustration for formula set up and help.

It is a good idea to calculate both average and median GPG values from your data. We all know that an odd high value can impact the average calculation. May be your CEO is a female and her high $$$ salary thus she bumps up the average female pay significantly.

Firstly, calculate the median pay for both male & female groups. *Unfortunately, Excel doesn’t have a MEDIANIFS function. *So, use the below formula instead:

**=MEDIAN(IF(staff[Gender]="Male",staff[Annualized Full-time Salary]))**

**Caution: Array formula**

After typing the formulas, press **CTRL+Shift+Enter **to get the correct result.

Change the gender value to Female for the respective median salary.

Once both medians are calculated, you can easily calculate the gender pay gap (both in dollars and percentage) using the same formulas as above.

Click here to download my **Gender Pay Gap calculator template**. Copy and paste your data and the file calculates the GPG automatically.

Once you have calculated the Gender Pay Gap in dollars, just divide the number with total annual hours of work. In most countries, this would be 2080 hours (ie 52 weeks times 40 hours per week).

So, for example, if you have a pay gap of $3,117, then the hourly pay gap is $1.50

This means, female staff are earning $1.50 less than their male counterparts *every hour.*

A negative GPG value indicates that your female staff are paid more (on average or median basis) compared to the male staff.

While Gender Pay Gap offers a great insight into the compensation of men vs women employees, it has a few limitations.

**GPG doesn’t explain any hierarchical distribution issues.**If you have a lopsided distribution of staff in your organization (may be more female staff at lower-level positions and more male staff in senior positions), GPG doesn’t expose this issue. I recommend visualizing the male vs. female distribution by salary bands or seniority for a better insight in to these issues.**A low or zero Gender Pay Gap is not enough.**If you want an equitable and fair organization, aiming for a zero gender pay gap at aggregate level is not enough. You need to examine GPG by:- department level GPG
- city / location level GPG
- manager vs. non-manager GPG
- new hires vs. existing staff GPG

**GPG is meaningless for small organizations**. If your total headcount is less than 30, GPG calculations can be meaningless or less insightful.

Gender Pay Gap is a key metric (KPI) in HR data analysis. Calculating, measuring and tracking GPG is helpful to understand any underlying pay issues in your organization. But don’t forget to explore the staff distribution, hiring patterns and historical trends to fully understand your data.

For more on HR data analysis, check out below articles:

- 9 Box Grid & talent mapping analysis with Excel
- Understanding Employee Churn using Excel
- Employee training tracker with Excel
- HR Dashboard using Excel – Video
- HR Dashboard using Power BI

The post How to calculate the Gender Pay Gap using Excel Formulas? (Free Calculator Template) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>What is weighted average?

Wikipedia defines weighted average as, "The weighted mean is similar to an arithmetic mean ..., where instead of each of the data points contributing equally to the final average, some data points contribute more than others."

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

The post Weighted Average in Excel [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Weighted average or weighted mean is defined as [from wikipedia],

The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.

…If all the weights are equal, then the weighted mean is the same as the arithmetic mean.

**Well, it is because, in some situations normal averages give in-correct picture.** For eg. assume you are the CEO of ACME Widgets co.. Now you are looking annual salary report and being the numbers-gal you are, you wanted to find-out the average salary of your employees. You asked each department head to give you the average salary of that department to you. Here are the numbers,

Now, the average salary seems to be $ 330,000 [total all of all salaries by 5, (55000+65000+75000+120000+1200000)/5 ].

You are a happy boss to find that your employees are making $330k per year.

**Except, you are wrong.** You have not considered the number of employees in each department before calculating the average. So, the correct average would be $76k as shown above.

There is no built-in formula in Excel to calculate weighted averages. However, there is an easy fix to that. **You can use SUMPRODUCT formula**. By definition, SUMPRODUCT formula takes 2 or more lists of numbers and returns the sum of product of corresponding values. [related: Excel SUMPRODUCT Formula – what is it and how to use it?]

**So, if you have values in B4:B8 and the corresponding weights in C4:C8,** you can use SUMPRODUCT like this to get weighted average.

**=SUMPRODUCT(B4:B8, C4:C8)**

**Caution: **However, the above method works only if C4:C8 contains weights in percentages(%) totaling to 100%.

`=SUMPRODUCT(<your values>, <your weights>)`

May be your weights are more than 100 percent. Or may be they are less than 100 percent. In both cases, you can use the below formula variation.

**=SUMPRODUCT(B15:B19, C15:C19) / SUM(C15:C19) **

The idea is to divide the total of weights with the SUMPRODUCT result so that we can adjust Weighted Average as the weights don’t add up to 100 percent.

`=SUMPRODUCT(<your values>, <your weights>) / SUM(<your weights>)`

If you have count of observations instead of weights, you can still use the SUMPRODUCT formula to calculate weighted average in Excel.

Here is the formula for above example:

**=SUMPRODUCT(B26:B30, C26:C30) / SUM(C26:C30) **

Notice that this formula is same as the formula for weighted average with weights not adding up to 100 percent.

`=SUMPRODUCT(<your values>, <your counts>) / SUM(<your counts>)`

Let’s say you have city wise observations and weights. And you want to calculate the weighted average, only for ** Boston **values. In this case, you can use a variation of the formula like below:

**=SUMPRODUCT((C5:C16)*(B5:B16=F5),D5:D16)/SUMIFS(D5:D16,B5:B16,F5)**

**How does this formula work?**

- SUMPRODUCT calculates the total value for BOSTON by summing up C5:C16 (value column) where B5:B16 is Boston (highlighted portion of the formula) and multiplies that with the counts.
- So in the above example, this will just give us the total of Boston – ie 218,600
- We then divide this with the total count of Boston (using the SUMIFS formula) – ie 400
- This results in the weighted average for Boston values alone – ie 546.50

For more information on how the conditions work inside SUMPRODUCT formula, please read this article.

In this workbook, you can find 4 examples on how to to calculate weighted average in excel. Go ahead and download it to understand the formulas better.

Here is a video with Weighted Average formula explained. Please watch it below to learn more. Alternatively, head to my YouTube page to see the weighted averages in Excel video.

Weighted averages are a great way to explain data and every data analyst should know how and when to use them with their data. Apart from Weighted Average, I suggest learning how to use moving average and average of top n values. These will help you explain the data and trends to your audience better.

What do you use it for? What kind of challenges you face? Do you apply any tweaks to weighted average calculations? **Please share your ideas / tips using comments.**

- Syntax and Examples of Excel Average Formula
- Formula for Average of Top 5 values
- Calculating Moving Average in Excel
- Using SUBTOTAL formula and calculating averages
- Showing Averages in Pivot Tables

The post Weighted Average in Excel [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post How to get non-adjacent columns with FILTER function in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Imagine you have a table data named “staff” and you want to see all the staff who joined in year 2021. We can use below FILTER function for that.

**=FILTER(staff, YEAR(staff[date of join])=2021)**

This will provide a list of all staff who joined in year 2021, as depicted below.

But we don’t want all columns, just ID, Gender, Salary and Leave Balance.

To see just columns 1,2,7 & 8 of this filtered data, we can use below formula.

**=CHOOSECOLS(
FILTER(staff, YEAR(staff[date of join])=2021),
1,2,7,8)**

This will give you exactly what you need without anything else.

Say, you do want the columns 2,6,8&9 but you want them to show up in the order 6,8,2&9 in the final output.

You can still use the CHOOSECOLS function like below.

**=CHOOSECOLS(**

FILTER(staff, YEAR(staff[date of join])=2021),

6,8,2,9)

If you want to use a range of column names and show filtered data for only those columns, we can use XMATCH along with CHOOSECOLS and FILTER, as demoed below.

- Set up your column headers in a range like Z5:AC5
- Now, we can use XMATCH to find the positions of these headers. =XMATCH(Z5:AC5, staff[#headers])
- When you pass the result of XMATCH to CHOOSECOLS, you can pick these columns.

**=CHOOSECOLS(
** ** FILTER(staff,YEAR(staff[Date of Join])=2021),
XMATCH(Z5:AC5,staff[#Headers]))**

- Let’s go inside out.
- The FILTER() function gets all the staff data for people whose joining date is in 2021.
**Range Z5:AC5 holds the names of the columns we want to see.**- XMATCH(Z5:AC5, staff[#Headers]) will tell you the column numbers for the columns you want by looking them up in the table header row.
- CHOOSECOLS() will then return those exact columns

- How to use FILTER function in Excel, FILTER function video tutorial
- How to use XLOOKUP function in Excel
- How to use tables in Excel

The post How to get non-adjacent columns with FILTER function in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post What is XLOOKUP and how to use it in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>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.

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:

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")**

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.

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.

```
=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.

```
=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.

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.

Refer to below handy table for some extra tips on using the partial matching feature of XLOOKUP.

Situation | Formula | Explanation | Sample Result |
---|---|---|---|

Name 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 pattern | Javed, $2277 |

Name 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, $1610 |

Name 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, $726 |

Name 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 d | Javed, $2277 |

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.*

In this case, you can use the new FILTER() function in Excel to get the second or all matching items.

Here is the formula.

**=FILTER(D4:D21,B4:B21="Johnson")**

For more information on FILTER function, refer to this article.

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)**

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.

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.

**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.

*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.

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].

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.

Try this if you want to search from bottom to top. The default direction is top down (1).

Here is a simple but effective video tutorial on how to use XLOOKUP function and how it can replace VLOOKUP and INDEX+MATCH functions. You can also watch the xlookup tutorial on my channel.

**Click here to download the 13 XLOOKUP examples file**.

And one more example file, this with INDEX+MATCH replacements.

In my opinion, XLOOKUP is a terrific function and a must have for any data professional. It is part of my **essential Excel formulas list for data analysts**.

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.

This post is part of my data analysis with Excel series. Please learn other topics too and improve your data skills with Excel.

- How to use Excel Tables – 13 time saving tips
- How to use Dynamic Array functions in Excel such as Filter, Unique, Sort etc.
- Working with Power Query in Excel to save time and shine at work
- Know more about XLOOKUP with my course

The post What is XLOOKUP and how to use it in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>