إنتاجية

5 Useful Advanced Excel Formulas You Should Know


As you continue to use Microsoft Excel for tracking, budgeting, or inventory, you probably learn a lot more about the available functions and their formulas. But it can still be overwhelming or even scary to figure out something new. This guide provides a handful of useful functions and their formulas for when you’re ready to dive into new territory.

1. Look Up Values in Your Sheet: XLOOKUP

When you want to look up data based on other data in a large spreadsheet, XLOOKUP is your go-to function. Different than VLOOKUP, which can only look up values from left to right, XLOOKUP can look up values from left to right or right to left, making it a more flexible tool.

Note: as of this writing, XLOOKUP is only available for Microsoft 365 subscribers. For other Excel versions, check out the INDEX and MATCH combination in the next section.

The syntax for the function is:

XLOOKUP(lookup_value, lookup_range, return_range, not_found, match_mode, search_mode)

Only the first three arguments are required. Listed below is a description of each argument:

  • Lookup_value: the value to search.
  • Lookup_range: the range containing the value to search.
  • Return_range: the range containing the value to return.
  • Not_found: the text to return if the value is not found. “#N/A” is the default if omitted.
  • Match_mode: the type of match using “0” for an exact match and “#N/A” for none found (default if omitted), “1” for an exact match and the next smaller item if none found, “-1” for an exact match and the next larger item if none found, or “2” for a wildcard match using a question mark, asterisk, or tilde.
  • Search_mode: The search mode using “1” to start at the first item (default if omitted), “-1” to start at the last item, “2” when “lookup_range” is in ascending order, or “-2” when “lookup_range” is in descending order.

For example, we are looking up the sales for sector 2, so we’re using the following formula:

=XLOOKUP(2,A19:A24,D19:D24)

Breaking down the formula, 2 is the “lookup_value,” A19:A24 is the “lookup_range,” and D19:D24 is the “return_range” argument.

The result, 74,000, is the correct corresponding value for sector 2.

If you want something more dynamic, where you look up a value in a cell instead of a constant, then receive the updated result, whenever you change the value in that cell, see the following example:

We are replacing the “lookup_value” of 2 with cell F19:

=XLOOKUP(F19,A19:A24,D19:D24)
Xlookup formula with a cell reference

When we enter 2 in cell F19, we receive the result 74,000, and if we enter a different value, such as 5, we automatically receive the updated result, which is 61,000.

Xlookup formula with a cell reference updated

You can include the “not_found” argument so that you don’t have to look at an error message if a match isn’t found. We are adding “No dice” for the argument.

=XLOOKUP(F19,A19:A24,D19:D24,"No dice")

When a value is entered in cell F19 that does not have a match, you will see your message instead of “#NA.”

Xlookup formula with a Not Found message

2. Alternative Value Lookup in a Sheet: INDEX and MATCH

While XLOOKUP gives you a solid way to look up values, currently, it’s only available for Microsoft 365 Excel subscribers. If you use a different version of Excel, you can use INDEX and MATCH to do the same thing.

With the INDEX function, the value in a cell is returned based on the position you enter in the formula. The syntax for INDEX is:

INDEX(range, row_number, column_number)

With the MATCH function, a position for a value you enter in the formula is returned. The syntax for MATCH is:

MATCH(value, range, match_type)

To combine these two functions and their formulas, place the MATCH formula within the INDEX formula as the lookup position (“row_number” and “column_number”).

Using the same data as the XLOOKUP example above, we want to see the sales for the sector we enter into cell F19. The formula is:

=INDEX(D19:D24,MATCH(F19,A19:A24))
Index and Match formula

To break down this formula, we start with INDEX and its “range” argument, which is D19:D24. This is the range containing the result we want.

The MATCH formula uses F19 as the “value” argument and A19:A24 as the “range” argument containing that value.

When we enter 4 in cell F19, we receive the result 75,000, which is correct.

Index and Match formula result

If we enter a different sector in cell F19, such as 6, the formula updates automatically to give the correct value of 58,000.

Index and Match formula result updated

Tip: try entering functions into a cell by pressing Shift + F3. The “Insert Function” menu lets you search and select functions. Check out our Excel keyboard shortcuts cheat sheet for more timesavers.

3. Add or Count With Criteria: SUMIF, SUMIFS, COUNTIF, and COUNTIFS

The SUM and COUNT functions in Excel are designed to add numbers and count cells, but they are limited to simple calculations. Variations of these functions allow you to sum or count using criteria. For instance, you can total only numbers greater than 12 or count only cells containing the name Bill.

With SUMIF and COUNTIF, you can add or count with one condition, and with SUMIFS and COUNTIFS, you can add or count with multiple conditions. Listed below is the syntax for each:

SUMIF

SUMIF(range, criteria, sum_range)

COUNTIF

COUNTIF(range, condition)

SUMIFS

SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2,…)

COUNTIFS

COUNTIFS(condition_range1, condition1, condition_range2, condition2,…)

See below for an example of each function.

To add the numbers in our range B2 through B7 only for those numbers greater than 12 we are using the SUMIF function and this formula:

The result is 31, as the formula added 16 and 15, the only numbers greater than 12 in the dataset.

Sumif formula and result

In the next example, we are counting the cells with values less than 12 in the same range, B2 through B7, with the following formula:

The result for this formula is 4, as that’s the number of cells in the range with values less than 12.

Countif formula and result

Let’s take it up a notch with SUMIFS and use two conditions. To add the numbers in cells B2 through B7 for only those that signed up for our newsletter (Y) in cells C2 through C7 and whose city is San Diego in cells D2 through D7, we are using the following formula:

=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")

The result for this formula is 19, as there are only two customers who have a Y for the newsletter and San Diego for the city. Bill Brown has 11 items and Sue Smith has 8 for a total of 19.

Sumifs formula and result

For an example using the COUNTIFS function, we are counting the number of cells with the same two conditions described above. We are counting the number of customers who signed up for the newsletter (Y) and whose city is San Diego.

=COUNTIFS(C2:C7,"Y",D2:D7,"San Diego")

As expected, the result is 2 for Bill Brown and Sue Smith, the only newsletter signups in San Diego.

Countifs formula and result

4. Test Data With Various Conditions: IFS

The IF function in Excel is a powerful tool for testing data against conditions. For instance, you could use it to display a letter grade for a student’s numeric grade or a “Yes” if a salesperson brings in enough revenue to earn their bonus and a “No” if they do not.

The problem with the IF function is that to test your data against multiple criteria, you must nest all IF statements together. To remedy this mass confusion, use the IFS function.

Similar to SUMIFS and COUNTIFS above, IFS lets you add multiple criteria to your formula in a clear and easy-to-read way.

The syntax is IFS(test1, if_test1_true, test2, if_test2_true, …), where you can test up to 127 different criteria.

Using one of our example scenarios, we are starting simple to indicate bonuses. If the amount in cell B2 is above 20,000, display “Yes.” If not, display “No.” The formula becomes:

=IFS(B2>20000,"Yes",B2<20000,"No")

In the first test, if the value in B2 is greater than 20,000, the result if it’s true is “Yes.” In the second test, if the value in B2 is less than 20,000, the result if it’s true is “No.”

Luckily, our salesperson receives his bonus. As his sales are 21,000, a “Yes” goes into the Bonus column.

Ifs greater than formula

Copy down the formula if you have a list like our example. Drag the fill handle in the bottom-right corner of the cell down to the remaining cells for the other salespeople. The formula automatically updates to accommodate the different cell references.

Dragging the Ifs formula down

For one more IFS example, we have our list of managers on duty for each day of the week. When the current day displays in cell D2, the corresponding manager’s name displays in cell E2. The formula becomes:

=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)

This shows that if the value in cell D2 is Monday, display the name in cell B2, if the value in cell D2 is Tuesday, display the name in cell B3, if the value in cell D2 is Wednesday, display the name in cell B3, and so on.

Ifs equal to formula

As you change the day of the week in cell D2, the corresponding name displays for the manager on duty in cell E2.

Ifs equals to formula updated

With a small amount of work creating the formula upfront, you can reap the rewards of some automation in your Excel sheet.

5. Filter Using Multiple Criteria: FILTER

As an Excel user, you probably know that the application comes with a built-in filter feature. However, if you want to filter your data using a condition or even multiple criteria, you’ll need to use the FILTER function. This flexible function and its arguments can help you narrow large datasets in just minutes.

The syntax for the function is FILTER(range, range=criteria, if_empty), where only the first two arguments are required for the dataset plus the criteria and its container range. The third argument is useful if you want to return something in particular if the formula yields zero results, such as “no data” or “none.”

It’s important to know how the FILTER function works with a single condition first before adding multiple criteria. For example, you can filter the data in cells A2 through C9 by Harold Hill which exists in B2 through B9. The formula for this is:

=FILTER(A2:C9,B2:B9="Harold Hill")
Filter formula with one condition

We have two results for Harold Hill. Easy, right?

Let’s add more conditions to the formula. Multiple criteria are added using an asterisk (*) for AND and a plus sign (+) for OR.

As an example, we are filtering for both Harold Hill and Electronics using the following formula:

=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))

To break down the formula, A2:C9 is the dataset, B2:B9=”Harold Hill” is the first condition, the asterisk represents AND, and A2:A9=”Electronics” is the second condition.

We received one result with our filter, as the formula must match both conditions, Harold Hill and Electronics.

Filter formula an asterisk for And

In another example, you can filter for Apparel or Automotive:

=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))

Breaking this formula down, A2:C9 is the dataset, A2:A9=”Apparel” is the first condition, the plus sign represents OR, and A2:A9=”Automotive” is the second condition.

This time, we received two results, as the formula must match either condition – not both.

Filter formula with a plus sign for Or

Tip: to learn more advanced Excel skills, check out our Power Query and Power Pivot guides.

Frequently Asked Questions

How do I find the structure for a function’s formula directly in Excel?

Excel offers a built-in tool for finding the function you need. With it, you can see the syntax for that function’s formula.

Select an empty cell, go to the “Formulas” tab, and choose “Insert Function” on the left side of the ribbon. Type the function in the search box at the top and click “Go.” When you see the function you want, select it to see its description and syntax at the bottom of the box.

How can I debug a formula that isn’t working right?

Select the formula you’re struggling with and go to the “Formulas” tab. Choose “Evaluate Formula” in the Formula Auditing section of the ribbon.

You’ll see your formula in the Evaluate Formula window with a portion of it underlined. Click “Evaluate” to see the underlined portion calculated. Continue this process to move through each part of your formula to the result. This allows you to see how the formula is processed and where the mistake may be.

How can I find out the reason I’m receiving a formula error?

When you see an error for a formula you’ve created, you can do two things to get more information. First, click the error button that appears next to the cell, then select “Help on This Error” to obtain more details from Microsoft in the sidebar that appears.

Alternatively, select “Show Calculation Steps” to open the Evaluate Formula window described above. You’ll see the calculation result and the process to get there, which should help determine the cause of the error.

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Sandy Writtenhouse
Sandy Writtenhouse

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

مقالات ذات صلة

اترك تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *

زر الذهاب إلى الأعلى