10 Excel Functions Every Analyst Should Know

The 10 functions every analyst uses every week: XLOOKUP (replaces VLOOKUP), INDEX MATCH (legacy alternative), SUMIFS (sum with conditions), COUNTIFS (count with conditions), IFERROR (wrap errors), TEXT (format strings), SUMPRODUCT (OR-conditions, weighted averages), LET (named sub-expressions), FILTER (spill array filter), UNIQUE (spill array deduplicate). Master these and 80% of daily analysis gets faster.

XLOOKUP searches any column and returns from any column. VLOOKUP only searches the leftmost column and returns from a column to the right. XLOOKUP defaults to exact match; VLOOKUP defaults to approximate. XLOOKUP has a built-in not-found value; VLOOKUP returns #N/A. Use XLOOKUP for any new workbook; keep VLOOKUP only in legacy files with hundreds of existing formulas.

SUMIFS for simple AND-sums across one or two columns. SUMPRODUCT for OR-conditions across ranges, weighted averages, and multi-column conditional counts. SUMPRODUCT is the right tool when the conditions are complex or when AND-only logic doesn't fit.

LET assigns a name to a sub-expression inside a single formula. It improves readability for long formulas and runs faster because named sub-expressions are only computed once. Useful for complex XLOOKUP or LAMBDA chains. Excel 365 only.

INDEX returns the value at a given position in a range. MATCH returns the position of a value in a range. Together: =INDEX(return_column, MATCH(lookup_value, lookup_column, 0)). Works in every Excel since 2007. Pick INDEX MATCH when the audience is on Excel 2016 or older; pick XLOOKUP otherwise.