Advanced MS Excel Formulas and Functions

Microsoft Excel is a powerful tool for working with data, and the two things that help the most are formulas and functions. These help you with the most complex calculations and data visualisation, increasing your efficiency significantly. 

If you are already familiar with the must-know simple Microsoft Excel formulas and functions like SUM, it’s time to learn advanced ones. Here are some of them. 

5 Advanced MS Excel Formulas and Functions

Let’s look at some of the most used MS Excel functions and formulas that can be considered advanced skills. We have included examples that will help you learn visually.  

1. VLOOKUP

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The VLOOKUP function finds a value in the first column of a designated table and returns a value in the same row from another column you provide. It lets one search a table for information, such as the price of a good or the student’s grade, depending on an ID.

Explanation of the Syntax

• lookup_value: The value you want to look for in the first column of the table.
• table_array: The range of cells that make up the table where you’re searching. This includes the column containing lookup_value and the column(s) containing the result.
• col_index_num: The column number (starting from 1 for the leftmost column) in table_array from which to return the value.
• [range_lookup]: An optional argument. If set to TRUE (or omitted), VLOOKUP will look for an approximate match. If set to FALSE, it will only return an exact match.

Example

If we want to find the price of the product with ID 102 (Bananas), we can use the following formula:

=VLOOKUP(102, A2:C4, 3, FALSE)

Result: $1.50

2. INDEX and MATCH

Syntax: =INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))

Combining MATCH and INDEX lets you search for a value in one column and return a value from another column within the same row. It’s like VLOOKUP, but it’s more flexible because the lookup column doesn’t have to be the first column of the range.

Explanation of the Syntax

INDEX(array, row_num, [column_num]):

• array: The range of cells from which you want to retrieve a value.
• row_num: The row number in the array from which to return a value.
• [column_num]: Optional if the array is a single column; specifies the column number if the array has multiple columns.

MATCH(lookup_value, lookup_array, [match_type]):

• lookup_value: The value you want to find in the lookup array.
• lookup_array: The range of cells where Excel should search for the lookup value.
• [match_type]: Specifies the type of match:
• • 0 for an exact match.
  • 1 for the largest value less than or equal to the lookup value (requires data to be sorted in ascending order).
  • -1 for the smallest value greater than or equal to the lookup value (requires data to be sorted in descending order).

Example

We want to find the price of “Bananas” in the “North” region.

To achieve this, we will use a combination of INDEX, MATCH, and an additional feature called array formula (Ctrl + Shift + Enter on Excel versions that require it) to search both the Product and Region columns simultaneously. Here is the formula used in our example table: 

=INDEX(E2:E8, MATCH(1, (B2:B8=”Bananas”) * (C2:C8=”North”), 0))

Result: $1.60

3. COUNTIF

Syntax: =COUNTIF(range, criteria)

The COUNTIF Excel function counts the number of cells in a specified range that meet a particular condition or criteria. It’s commonly used to count entries based on specific text, numbers, or logical conditions.

Explanation of the Syntax:

1. range: The group of cells where you want to count matches.
2. criteria: The condition that determines which cells to count. This can be a number, text, expression, or cell reference.

Example

From this table, we will try to find how many times “John” has sold products in the “North” region using COUNTIF. The formula for this will be: 

=COUNTIFS(A2:A11, “John”, B2:B11, “North”)

As you can see, we will only need to select the rows for a range that the criteria is in. 

Result: 4

4. TRIM

Syntax: =TRIM(text)

The TRIM feature will help you to show your table’s cells in a more consistent manner if their spacing is uneven. It eliminates all unnecessary spaces from a text string, therefore leaving just single spaces between words.

Explanation of the Syntax:

• text: This is the input text or cell reference containing the text from which you want to remove extra spaces.

Example

Several cells in this table have irregular spacing. With the TRIM function, we can make it look better like the following:

5. CHOOSE

Syntax: =CHOOSE(index_num, value1, [value2], …)

Based on the index number you indicate, the CHOOSE function chooses and returns a value from a list of values.

Explanation of the Syntax:

• index_num: A number that specifies which value to return. It must be a whole number between 1 and the number of values provided in the function.
• value1, [value2], …: The list of values from which the function chooses. You can provide text, numbers, cell references, or formulas as values.

Example

Suppose you have a table that represents months:

You want to use the CHOOSE function to return the month based on an index number.

1. In Cell A6, enter the index number 3.
2. In Cell B6, write the following formula:=CHOOSE(A6, “January”, “February”, “March”, “April”, “May”)

If A6 = 3, the formula returns “March” because it’s the third value in the list.

This is useful when you need to pick a value from a predefined set of choices.

Conclusion

There are many more advanced MS Excel functions and formulas that you need to master in order to be an expert. If you are willing to learn those, it’s better to get help from experts themselves. At Sikkhon, you can learn from the best thanks to the well-crafted courses on many subjects. Learn Excel and other Microsoft Office products at your own pace with Sikkhon.