Top 5 Advanced Excel Formulas with Examples
1. VLOOKUP with Wildcards
Purpose: Search for partial matches in a lookup table.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: Find a product price using a partial product name.
Scenario: You have a product list in cells A2:B10, with product names in column A and prices in column B.
Formula: =VLOOKUP("*"&C1&"*", A2:B10, 2, FALSE)
Explanation: If C1 contains "pen", the formula searches for any product name containing "pen" (e.g., "Blue Pen") and returns its price. The asterisks (*) allow partial matching.
2. INDEX and MATCH Combination
Purpose: Flexible lookup for row and column references.
Syntax: =INDEX(range, MATCH(lookup_value, lookup_range, 0))
Example: Retrieve a student’s score based on their ID.
Scenario: Student IDs are in A2:A10, names in B2:B10, and scores in C2:C10. Find the score for ID in D1.
Formula: =INDEX(C2:C10, MATCH(D1, A2:A10, 0))
Explanation: MATCH finds the row number of the ID in A2:A10, and INDEX retrieves the corresponding score from C2:C10.
3. IFERROR for Error Handling
Purpose: Handle errors gracefully in formulas.
Syntax: =IFERROR(value, value_if_error)
Example: Avoid #DIV/0! errors in division.
Scenario: Divide values in A1 by B1.
Formula: =IFERROR(A1/B1, "Error")
Explanation: If A1/B1 results in an error (e.g., division by zero), the formula returns "Error" instead.
4. SUMIFS for Conditional Summing
Purpose: Sum values based on multiple criteria.
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example: Sum sales for a specific region and product.
Scenario: Sales data in A2:C100 (Region in A, Product in B, Sales in C). Sum sales for "North" region and "Laptop" product.
Formula: =SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Laptop")
Explanation: Sums values in C2:C100 where A2:A100 is "North" and B2:B100 is "Laptop".
5. TEXTJOIN for Combining Text
Purpose: Concatenate text with a delimiter.
Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Example: Combine names in a range with commas.
Scenario: Names in A2:A5. Combine them into one cell.
Formula: =TEXTJOIN(", ", TRUE, A2:A5)
Explanation: Joins names in A2:A5 with a comma and space, ignoring empty cells.
