List of Top 10 Advanced Excel 2019 Formulas with example

 

Here are ten advanced Excel formulas with examples:

1.   INDEX-MATCH :- =INDEX(A2:A10,MATCH("John",B2:B10,0))

This formula searches for the name "John" in the range B2:B10 and returns the corresponding value in the range A2:A10.

 

2.   SUMIFS :-

=SUMIFS(C2:C10,A2:A10,"John",B2:B10,"Red")

This formula adds up the values in the range C2:C10 that correspond to the name "John" in column A and the color "Red" in column B.

 

3.   IFERROR :-

=IFERROR(A2/B2,"Error: Divide by zero")

This formula divides the value in cell A2 by the value in cell B2, and if there is an error (such as dividing by zero), it displays the custom error message "Error: Divide by zero".

 

4.   CONCATENATE :-

=CONCATENATE("John"," ","Doe")

This formula combines the text strings "John", a space, and "Doe" into one cell.

 

5.   COUNTIFS :-

=COUNTIFS(A2:A10,"John",B2:B10,"Red")

This formula counts the number of times the name "John" appears in column A and the color "Red" appears in column B.

 

6.   AVERAGEIFS :- =AVERAGEIFS(C2:C10,A2:A10,"John",B2:B10,"Red")

This formula calculates the average of the values in the range C2:C10 that correspond to the name "John" in column A and the color "Red" in column B.

 

7.   CHOOSE :-

=CHOOSE(3,"Red","Green","Blue")

This formula returns the value "Blue" because it is in the third position in the list of values.

 

8.   VLOOKUP :-

=VLOOKUP("John",A2:B10,2,FALSE)

This formula searches for the name "John" in the range A2:B10 and returns the corresponding value in the second column (column B).

 

9.   HLOOKUP :-

=HLOOKUP("John",A2:B5,2,FALSE)

This formula searches for the name "John" in the range A2:B5 and returns the corresponding value in the second row.

 

10.                     ROUND :-

=ROUND(A2/B2,2)

This formula divides the value in cell A2 by the value in cell B2 and rounds the result to two decimal places.