10 Most Important Advanced Excel Formulas

Every data analyst has to work on Microsoft Excel in his career. It is the most loved tool by data analysts across the world. Here are 10 Most Important Advanced Excel Formulas that any one working with data should know.

INDEX MATCH formula in Excel

If you want to locate a number in a table, INDEX MATCH formula is a powerful way. It is more flexible than the VLOOKUP or HLOOKUP formulas, and it can handle multiple criteria.

The INDEX MATCH formula has three arguments:-

1. The range: This is the range of cells that contain the value you want to look up.
2. The row number: This is the row number of the cell that contains the value you want to look up.
3. The column number:- Select the column where you want to find the value.

The INDEX MATCH formula works by first finding the row number of the cell that contains the value you are looking for. MATCH formula is used for this. The MATCH formula returns the position of the value you are looking for in the range you specified.

Once the INDEX MATCH formula has found the row number, it then returns the value in the cell in that row and column.

For example, imagine you want to get the price of “Poco M4 Pro” from the mobile prices data. 

IF formula in Excel

This IF is just like the conditional “if” statement in programming. If the condition is TRUE, one value is returned. It returns the other value when the condition is FALSE.

The IF formula has three arguments:

1. The condition:- This is the expression that you want to test. The condition can be any logical expression, such as “A2>B2” or “C2=”Yes””.
2. The value if true:- Returned if the condition is TRUE.
3. The value if false:- Returned if the condition is FALSE.

Example:- Suppose you have a cell which has the value “Yes”. 

Now, you want to return “Approved” if the value is “Yes”. For any other value “Not Approved” should be returned.

The IF formula would look like this:

=IF(A2=”Yes”,”Approved”,”Not Approved”)

In this formula, the condition is “A2=”Yes””. This means that the formula will check if the value in cell A2 is equal to “Yes”. 

If the value in cell A2 is equal to “Yes”, the formula will return the text “Approved”. If the value in cell A2 is not equal to “Yes”, the formula will return the text “Not Approved”.

Examples of how you can use the IF formula in Excel:-

• To check if a value is greater than or equal to another value:

=IF(A2>=B2,”Yes”,”No”)

• To compare two values and return the larger value:

=IF(A2>B2,A2,B2)

• To make a decision based on the value in a cell:

=IF(A2=”Yes”,”Approved”, “Not Approved”)

SUMPRODUCT Formula in Excel

Its a formula that does both, add and multiply, the cells in the columns / rows (it’s in the name!). It is used to sum the products of corresponding numbers in one or more ranges.

The SUMPRODUCT formula has three arguments:

1. The first range: This is the first range of numbers that you want to multiply.
2. The second range: This is the second range of numbers that you want to multiply.
3. Optional: An array constant. An array of numbers that you want to multiply.

The SUMPRODUCT formula works by first multiplying the corresponding numbers in the two ranges. It then sums the products.

The SUMPRODUCT formula can also be used to perform a variety of other calculations, such as:

1. Calculating the total revenue from a sales table
2. To calculating the total number of employees from a payroll table
3. Calculating the average price of a product from a price table

CHOOSE formula in Excel

A very important formula in advanced excel. It simply chooses the cell from the range that you pass based on their index (ID). You only need to change the index number once you write this formula. The rest values will be same and you will get your result.

The CHOOSE formula has three arguments:

1. The index number (ID): This is the number of the value or formula that you want to select. The index number can be any number between 1 and 254.
2. The value1: This is the first value or formula that you want to select.
3. Optional: The value2, value3, … additional values or formulas that you want to select.

Example:-

Suppose you have a list of mobile phones and their prices. You want to get the price of the phone in B2 cell. The prices are in the column C. 

The formula will be:-

=CHOOSE(A2,C1,C2,C3,C4)

NOTE:

1. To write this formula, you first select the index cell. 
2. Then you can select the range of cells in which you want to find the price. You can do this by clicking each cell and then typing “,”.

TEXTJOIN formula in excel

The TEXTJOIN formula in Excel is used to combine multiple text strings into a single text string with a delimiter (Like a comma, space, etc.) between them.

The TEXTJOIN formula has four arguments:

1. The delimiter: This is the character that you want to use to separate the text strings.
2. Ignore empty: This is a Boolean value that determines whether empty cells should be ignored. If TRUE, empty cells will be ignored. If FALSE, empty cells will be included.
3. Text1: This is the first text string that you want to join.
4. [Text2]: This is an optional argument that allows you to specify additional text strings that you want to join.

The TEXTJOIN formula works by first joining the text strings in the order that they are specified. It then inserts the delimiter between each text string.

For example, let’s say you have three text strings:- “Hello”, “world”, and “!”. You want to use the TEXTJOIN formula to join these text strings into a single text string, with a space between each text string.

The TEXTJOIN formula would look like this:

=TEXTJOIN(” “,TRUE,”Hello”,”world”,”!”)

This formula would return the text string “Hello world!”.

NOTE: There should be no space between the TEXTJOIN and “(“. 

IFS formula in excel

Function:- It’s a more advanced version of IF formula. We can test multiple conditions with it unlike IF, where we can only test one. It will return different value based on the condition.

The IFS formula syntax:

=IFS(condition1,value1,condition2,value2,…)

1. condition1: This is the first condition that you want to test.
2. value1: return if condition1 is true.
3. condition2: second condition.
4. value2: return if condition2 is true.
5. …: This is an optional argument that allows you to specify additional conditions and values.

Working: The IFS formula checks each condition one by one. Then it returns the value where the condition is true. If no condition is satisfied, it returns “#NA”.

Examples

In this example, the IFS is used on the “A7” cell. Depending on the ID number in that cell, the formula will return the name of the mobile phone.

UNIQUE formula in excel

Function: Extract only unique values from the selected array of cells. It’s just like the set in python!

Syntax: 

=UNIQUE(range)

1. Range: Range of cells that you want to select the unique values from.

NOTES:

1. This formula ignores empty cells.
2. Does not preserve the order. That means the order will not be same.
3. It is not case sensitive.

LOOKUP formula in Excel

Function: This is a formula in Advanced Excel, that is similar to the INDEX MATCH formula which we saw first. It is a more advanced version. We can look up a value in a table and return the corresponding value from another column with it. 

Syntax:

=LOOKUP(lookup_value, table_array, column_index_num)

• lookup_value: Value that you want to look up in the table.
• table_array: Range of cells that contains the table.
• column_index_num: Number of the column in the table that contains the value that you want to return.

Working:

The LOOKUP formula will first look for the lookup_value in the first column of the table_array. 

The formula will return the value in the column_index_num column of the table_array that is in the same row as the lookup_value if it finds the lookup_value.

If the lookup_value is not found, the formula will return #N/A.

Example:

We have used the formula to look fof the age of the person in this example. The formula is: 

=LOOKUP(“John”,A2:C4,B2:B4)

NOTES:

1. The LOOKUP formula is case-sensitive.
2. It ignores empty cells.
3. The LOOKUP formula only looks up exact matches.

ROUND formula in excel

Function: This formula is used to round up the number. Basically if you get a number in points, you can round up that number to the next decimal.

Syntax:

=ROUND(number, num_digits)

1. number: number that you want to round.
2. num_digits: number of decimal places that you want to round the number to.

Working:- Let’s say you have a number 123.456789. You want to round it to two decimal places. The formula would look like this:

=ROUND(123.456789,2)

NOTES:

1. The ROUND formula ignores any digits after the specified number of decimal places.
2. It rounds numbers up or down depending on the value of the num_digits argument.

OFFSET combined with SUM or AVERAGE formula in excel

Function: OFFSET formula in Excel is used to create a reference to a range of cells that is offset from a specified range. 

Syntax:

=OFFSET(reference, rows, cols, height, width)

• reference: range of cells that you want to offset from.
• rows: number of rows to offset the reference by.
• cols: number of columns to offset the reference by.
• height: number of rows in the range that you want to return.
• width: number of columns in the range that you want to return.

Syntax for the SUM formula:

=SUM(range)

• range: range of cells that you want to sum.

Syntax for the AVERAGE formula:

=AVERAGE(range)

• range: range of cells that you want to average.

Example:- Assume you have a range of data in cells A1:C10. You want to calculate the average of the numbers in first column. The formula would look like this:

=AVERAGE(OFFSET(B3:D6,0,0,4,1))

This formula would first create an OFFSET formula that returns the range of cells A1:C10. It would then use the AVERAGE formula to calculate the average.

Concluding Thoughts

1. We discussed 10 of the most important advanced Excel formulas here.
2. These formulas are used generally in a group to create complex and powerful structures.
3. Learn Advanced Excel at Technogeeks and get job as a data analyst.