Agregador de canales de noticias

Compound Interest Formula in Excel

Chandoo (Pointy Haired Dilbert) -

Compound interest is defined as “the interest on savings calculated on both the initial principal and the accumulated interest from previous periods.” Classically, known as “interest on interest”, this is the most common type interest used in every day finance situations. 

To calculate compound interest

  • 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, let me explain the necessary Excel formulas to calculate compound interest using your data.

In this Article Compound Interest Excel Formula

Let’s say you borrow $5,000 at 5% interest rate for 10 years. The compounded value at the end of 10 years can be calculated with below Excel formula.

  • Cell D4 has principal value: $5000
  • Cell D5 has interest rate: 5%
  • Cell D6 has years: 10
=D4*(1+D5)^D6

We can also calculate just the interest portion with this formula:

=D4*(1+D5)^D6 - D4 Compounding Once per Month
or 'T' Times per Year

It is common for compounding to be done more than once per year. In such cases, you can use below Excel formula logic to calculate the compound interest.

Below example shows compounding 4 times per year (ie, once every quarter).

' D20 has Principal Amount ' D21 has Rate of Interest ' D23 has number of years ' D24 has compounding terms per years =D20*(1+D21/D23)^(D22*D23) Compounding Every 'x' Months

If you want to calculate the effect of compounding every ‘x’ months, you can just below logic.

 

' D34 has Principal Amount ' D35 has Rate of Interest ' D36 has number of years ' D37 has number of months per compounding =D34*(1+D35*D37/12)^(D36*12/D37) Calculating Compound Interest with FV Function

Instead of using the P*(1+R/T)^(N*T) formula, you can use the FV () function (Future Value) to calculate the compounded value over time.

Here are a few examples:

$5000, 5%, 10 years, compounding once per year 'FV Syntax: FV(Interest Rate per term, Number of terms, , Principal Amount) =FV(5%, 10,, -5000) Output: $8144.47 $5000, 5%, 10 years, compounding 4 times per year =FV(5%/4, 10*4,, -5000) Output: $8218.10 $5000, 5%, 10 years, compounding x times per year 'Cell A1 has the Compounding Terms x =FV(5%/A1, 10*A1,, -5000) Compound vs. Simple Interest

Simple interest is defined as Principal x Interest Rate. It doesn’t change over time.

On the other hand, Compound Interest changes over time, as we calculate interest ON interest too.

Here is a quick demo of how Simple & Compound Interests compare over 20 years time, for $5,000 borrowed at 5% rate of interest.

'SIMPLE INTEREST FORMULA =Principal * Rate_of_INTEREST 'COMPOUND INTEREST FORMULA =Principal * (1 + Rate_of_INTEREST)^number_of_YEARS Compounding Effect

“Compounding Effect” or that rapid growth of money over time often surprises people.

Imagine investing $5,000 at 5%, compounded annually  for 20 years. Below table shows the effect of compounding on your money.

To calculate compounded value for various years, we can use below formulas.

'LIST OF 20 YEARS =SEQUENCE(20) 'COMPOUNDED VALUE AT THE END OF EACH YEAR 'Amount is $5000, Rate of interest is 5% =5000 * (1+5%)^SEQUENCE(20)

The compounding effect is starkly visible in the below graph.

Effect of Frequency on Compounding

You might think how often we compound would have an impact on the final value. But it does little. 

For example, if we compare the outputs of  $5,000 compounded at 5% at various frequencies, at the end of 20 years, the values would be:

  • Once a year compounding: $13,266.49
  • Twice a year: $13,452.32
  • 4 Times a year: 13,507.42
  • 6 Times a year: $13,535.21
  • Every month (12 times): $13,563.20
  • Every week (52 times): $13,584.88
  • Every day (365 times): $13,590.48

The value hardly changes.

Below table shows how this looks over various time periods.

Interest Rate vs. Compounding

Interest rate on the other hand has a dramatic effect on the result of compounding. 

For example, $5000 invested at 8% will be almost $11 million in a century!

Compounding is CRAZY!!!
$5,000 invested today at 1% interest would be worth $13,500 in 100 years.
Same money, but invested at 8% would be a whopping $10.9 million!

Tweet

We can see the dramatic impact of rising interest rates on the compounded value with this table.

'Compounded value at various interest rates 'List of interest rates upto 20% =SEQUENCE(20)/100 'COMPOUNDED VALUE AT VARIOUS RATES 'Amount is $5000, Duration is 20 years 'Compounded once per year =5000 * (1+SEQUENCE(20)/100) ^ 20 Interest Rate vs. Compounding Graph Effect of Compounding with Regular Payments

We can use Excel to figure out the compounded value with regular payments easily.

For example, if you invest

  • $500 every month
  • at 8%
  • for 20 years

the final amount will be $294,510.21

To calculate this you can use the FV function, as shown below:

'FV Function Syntax =FV(INTEREST_RATE, NUMBER_OF_PAYMENTS, PAYMENT_AMOUNT) 'Example with $500 monthly payment for 20 years at 8% =FV(8%/12, 20 * 12, 500) 'OUTPUT =$294,510.21

Here you can see the calculations and yearly balances for such regular (monthly) investments.

Rule of 72: Time to Double

A common thumb rule used in compounding is rule of 72. 

RULE OF 72
To find out how long it takes for your money to double, divide 72 with rate of interest.
For example, at 8% interest rate, your money will double in 72/8 = 9 years.

Tweet

You can use this when you don’t have the luxury of Excel or a calculator nearby to quickly calculate how long it takes for your money to double.

But what if I want to calculate the EXACT time it takes?

In such cases, you can use the formula =LOG(2) / LOG(1+Rate of Interest).

'Time to Double 'Exact formula =LOG(2) / LOG(1+Rate_of_Interest) 'Approximate formula =72/(Rate_of_Interest*100) 'Example at 8% =LOG(2) / LOG(1+8%) =9.01 =72 / (8% *100) =9

In below example, you can see the rapid decrease in time it takes to double as the interest rate (rate of return) goes up.

Reverse of Compounding - The PV Function

We can use the PV (Present Value) function in Excel to calculate the principal value, given a compounded value.

For example, you want to save $100,000 for your daughter’s wedding, which you expect to be in 20 years. You expect the rate of interest to be 5%.

You want to know how much to save now to get $100k after 20 years.

Using the PV function as below, we can get that result.

'Reverse of Compounding 'Using PV Function to calculat the initial amount 'FUTURE AMOUNT = $100,000 'INTEREST RATE = 5% 'DURATION = 20 YEARS 'COMPOUNDING ANNUALLY =PV(5%, 20,,-100,000) =$37,688.95 Reverse of Regular Compounding - PMT Function

And we can use the PMT function to calculate reverse of the regular compounding.

Going back to the “saving for daughter’s wedding” case, you want to save up $100k for your daughter’s wedding in 20 years. You expect the interest rate to be 5%.

How much should you save every year?

or every month?

We can use the PMT function to figure out the regular amounts.

'Reverse of Compounding with Regular Payments 'Using PMT Function to calculat the regular payments from end value 'FUTURE AMOUNT = $100,000 'INTEREST RATE = 5% 'DURATION = 20 YEARS 'COMPOUNDING ANNUALLY =PMT(5%, 20,,, -100000) =$3,024.26 Compound Interest in Excel - VIDEO

Need to understand these formulas better? 

Check out my quick and to-the-point video on Calculating Compound Interest in Excel

Example Workbook with Compound Interest Calculations

I made an Excel file with over 20 examples (and more than 100 formulas). Click here to download the file and learn the concepts better.

Learn More Finance & Accounting Concepts

Check out below articles to learn more useful Accounting & Finance concepts with related Excel formulas.

Top 10 Accounting KPIs and How to Calculate them in Excel?

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

Read More Mortgage Calculator with Extra Payments – Excel Download

Recently, we got a new mortgage. And I wanted to...

Read More Calculate CAGR (Compounded Annual Growth Rate) using Excel [Formulas]

Lets talk about how we can use Excel to calculate...

Read More Offset() function to Calculate IRR for Dynamic Range

Offset() function to Calculate IRR for Dynamic Range When you...

Read More NPV() function to calculate Present Value

Today, let us learn how to use NPV() function in...

Read More Load More

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

How to convert test scores to letter grades in Excel?

Chandoo (Pointy Haired Dilbert) -

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

Step 1: Set up a Mapping Table for Letter Grade & Scores

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
Step 2: Calculate the letter grades using LOOKUP function

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.

How to get Letter Grades from Percentiles (relative grading)

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:

Step 1: Set up a percentile-wise mapping table for letter grades

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

Step 2: Calculate the Percentiles for each test score

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.

Step 3: Convert Percentiles to Letter Grade

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.
Download Test Score to Letter Grade Calculation Template

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.

Bonus: These formulas work in Google Sheets too!

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.

Things to keep in mind when calculating letter grades

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.
Next steps:

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.

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

How to calculate the Gender Pay Gap using Excel Formulas? (Free Calculator Template)

Chandoo (Pointy Haired Dilbert) -

Gender Pay Gap is the difference in pay for groups of men & women and usually based on the average or median salaries. We can use Microsoft Excel to quickly calculate the GPG (Gender Pay Gap) from your data. In this article, let me explain the process, Excel formulas and offer you a ready to use GPG calculator.

What is Gender Pay Gap?

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 How to calculate Gender Pay Gap in Excel?

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 - C4Generalized formula = average of male salary - average of female salary

Percentage Gender Pay Gap:

=(C3-C4)/C3Generalized formula = (average of male salary - average of female salary) / average of male salary Gender Pay Gap from raw data:

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:

Step 1: Set up your data in as a table

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.

Step 2: Calculate male & female average salaries:

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.

Step 3: Calculate the Gender Pay Gap in $s and %:

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

Step 4: Format everything

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.

Average vs. Median Gender Pay Gaps

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.

To calculate the Median Gender Pay Gap values in Excel:

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.

Download FREE Gender Pay Gap Calculator – Excel File

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

How to get the Hourly Pay Gap values?

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.

Our GPG is negative, what does it mean?

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

Limitations & Problems with Gender Pay Gap statistics:

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.
In conclusion,

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:

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.

Weighted Average in Excel [Formulas]

Chandoo (Pointy Haired Dilbert) -

Today we will learn how to calculate weighted average in Excel with percentages.

What is weighted average ?

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.

Why should you calculate weighted average?

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.

How to Calculate Weighted Average in Excel with Percentages

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.

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

WAvg Formula Pattern (use this with your data) =SUMPRODUCT(<your values>, <your weights>) What if my percentage weights don’t add up to 100%?

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.

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.

WAvg Formula Pattern when weights don’t add up to 100 percent =SUMPRODUCT(<your values>, <your weights>) / SUM(<your weights>) Weighted Average when you have counts instead of 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:

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

WAvg Formula Pattern when you have counts instead of weights =SUMPRODUCT(<your values>, <your counts>) / SUM(<your counts>) Weighted Average with Extra Conditions

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:

How does this formula work?

  1. 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.
  2. So in the above example, this will just give us the total of Boston – ie 218,600
  3. We then divide this with the total count of Boston (using the SUMIFS formula) – ie 400
  4. 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.

Download Weighted Average Calculation Example Workbook:

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.

Weighted Average in Excel – Formula Explained

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.

In Conclusion

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.

Do you use Weighted Mean / Weighted Average?

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.

More examples on Averages and Formulas:

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

How to get non-adjacent columns with FILTER function in Excel

Chandoo (Pointy Haired Dilbert) -

Excel 365’s FILTER() function is great for getting a cut of data that meets your criteria. But what if you need to filter and then show non-adjacent columns? Something like below. In this article, let me show you a few options to get discrete columns after filtering with the FILTER function.

Using CHOOSECOLS with FILTER()

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.

What if I need to get data, but the column order is different from original data…

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)
How to get columns from a list of header names

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.

  1. Set up your column headers in a range like Z5:AC5
  2. Now, we can use XMATCH to find the positions of these headers. =XMATCH(Z5:AC5, staff[#headers])
  3. 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])) How does this work…
  • 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
Learn more about these functions:

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

What is XLOOKUP and how to use it in Excel?

Chandoo (Pointy Haired Dilbert) -

Think of XLOOKUP as an improved version of VLOOKUP. In this article, learn all about the XLOOKUP 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.

  1. Write =XLOOKUP( in a cell
  2. For the lookup_value, Type the name of the salesperson in double quotes (ex: “Jackie”)
  3. 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
  4. Now for the lookup_array, select the names column of your data.
  5. and for the return_array, select the net sales column of your data.
  6. Close the brackets and hit enter.
  7. 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?

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,

  1. Use the formula =XLOOKUP(H4&”*”,B4:B21,D4:D21,”No such person”,2)
  2. Here H4 contains the first few letters of the name, i.e. Jam
  3. 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.
  4. Rest of the XLOOKUP parameters are as per usual.
  5. 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.

Here is the formula.

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

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

Horizontal Lookup with XLOOKUP:

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)

=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: Search mode

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

XLOOKUP – Video Tutorial:

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.

Download XLOOKUP examples – workbook

Click here to download the 13 XLOOKUP examples file.

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

Final thoughts on XLOOKUP

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.

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.

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

Subscribe to necesitomas.com agregador