15 Advanced Excel Formulas Every Professional Should Know

Once you've mastered SUM, VLOOKUP, and IF, these 15 advanced formulas will take your Excel skills to the professional level. Each one solves a real business problem that basic formulas can't handle.

1. SUMPRODUCT

Sum values based on multiple criteria without needing an array formula:

=SUMPRODUCT((A2:A100="Sales")*(B2:B100="North")*(C2:C100))

This sums column C only where column A is "Sales" AND column B is "North".

2. INDEX-MATCH (Two-Way Lookup)

=INDEX(C2:F10, MATCH("Product A",A2:A10,0), MATCH("Q2",C1:F1,0))

Looks up a value based on both a row and column header — like a grid lookup.

3. TEXTJOIN

Combine multiple cell values with a delimiter, skipping blanks:

=TEXTJOIN(", ", TRUE, A2:A20)

4. UNIQUE (Dynamic Array)

=UNIQUE(A2:A100)

Returns a list of unique values that automatically spills into adjacent cells.

5. SORT

=SORT(A2:C100, 3, -1)

Dynamically sorts data by column 3 in descending order.

6. FILTER

=FILTER(A2:D100, C2:C100>50000, "No results")

Returns rows where column C exceeds 50,000. Replaces complex Advanced Filter setups.

7. LET

Define variables within formulas to avoid repeating calculations:

=LET(total, SUM(B2:B100), avg, AVERAGE(B2:B100), total/avg)

8. LAMBDA

Create custom, reusable functions:

=LAMBDA(price, tax, price * (1 + tax))(100, 0.18)

Name it via Formulas → Name Manager to use it like a built-in function.

9. XLOOKUP with Multiple Returns

=XLOOKUP("Alice", A2:A100, B2:D100)

Returns multiple columns at once — spills across cells automatically.

10. INDIRECT

Create dynamic references from text strings:

=SUM(INDIRECT("Sheet_"&A1&"!B:B"))

Sums column B from a sheet whose name is in cell A1.

11. IFERROR + IFNA

=IFERROR(VLOOKUP(A2,Data!A:C,3,0), "Not found")

Handle errors gracefully. Use IFNA when you only want to catch #N/A errors.

12. COUNTIFS (Multiple Criteria)

=COUNTIFS(A2:A100,"Sales", B2:B100,">50000", C2:C100,"<>")

Count cells matching multiple conditions across multiple columns.

13. SEQUENCE

=SEQUENCE(10, 1, 1, 1)

Generates a sequence of numbers. Combine with other functions for dynamic lists, date ranges, and numbering.

14. CHOOSECOLS / CHOOSEROWS

=CHOOSECOLS(A1:F100, 1, 3, 5)

Select specific columns from a range — great for rearranging data without manual copying.

15. VSTACK / HSTACK

=VSTACK(Sheet1!A1:C10, Sheet2!A1:C10)

Vertically combine ranges from multiple sheets into one dynamic array.

Learning Strategy

Don't memorise all 15 at once. Pick the 3-4 that solve problems you face regularly, practice them in real spreadsheets, and gradually add more to your toolkit.