Advanced Excel Formulas – With Examples
1. VLOOKUP
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Example: =VLOOKUP(A2, A5:D20, 3, FALSE)
2. HLOOKUP
Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE)
Example: =HLOOKUP("Sales", A1:F5, 2, FALSE)
3. XLOOKUP
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array)
Example: =XLOOKUP(A2, A:A, B:B)
4. INDEX + MATCH
Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example: =INDEX(B2:B20, MATCH(E1, A2:A20, 0))
5. IF
Syntax: =IF(condition, value_if_true, value_if_false)
Example: =IF(A2>=50, "Pass", "Fail")
6. IFERROR
Syntax: =IFERROR(formula, value_if_error)
Example: =IFERROR(A2/B2, 0)
7. SUMIFS
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1)
Example: =SUMIFS(C:C, A:A, "East")
8. COUNTIFS
Syntax: =COUNTIFS(range1, criteria1)
Example: =COUNTIFS(A:A, "Completed")
9. TEXT
Syntax: =TEXT(value, format_text)
Example: =TEXT(A1, "dd-mm-yyyy")
10. LEFT / RIGHT / MID
Examples:
=LEFT(A1,4)
=RIGHT(A1,2)
=MID(A1,2,3)
11. CONCAT / TEXTJOIN
Examples:
=CONCAT(A1," ",B1)
=TEXTJOIN(",",TRUE,A1:A5)
12. LEN / TRIM
Examples:
=LEN(A1)
=TRIM(A1)
13. ROUND
Syntax: =ROUND(number, num_digits)
Example: =ROUND(A1,2)
14. TODAY / NOW
Examples:
=TODAY()
=NOW()
15. UNIQUE / FILTER
Examples:
=UNIQUE(A1:A20)
=FILTER(A1:C20, C1:C20>1000)
16. PMT (Loan EMI)
Syntax: =PMT(rate, nper, pv)
Example: =PMT(10%/12, 60, 500000)
17. NETWORKDAYS
Syntax: =NETWORKDAYS(start_date, end_date)
Example: =NETWORKDAYS(A1, B1)
18. INDIRECT
Syntax: =INDIRECT(ref_text)
Example: =INDIRECT("A"&B1;)
19. OFFSET
Syntax: =OFFSET(reference, rows, cols)
Example: =OFFSET(A1,1,1)
20. AGGREGATE
Syntax: =AGGREGATE(function_num, options, array)
Example: =AGGREGATE(9,5,A1:A20)