Top 10 Excel Formulas Every Data Analyst Must Know

Q: What is the difference between VLOOKUP and XLOOKUP?

VLOOKUP can only search left-to-right. XLOOKUP can search any direction, return multiple columns, and has built-in error handling. Available in Excel 365 and 2021+.

Q: When should I use INDEX MATCH instead of VLOOKUP?

Use INDEX+MATCH when the lookup column is not first, or when you need to look left. It never breaks when columns are rearranged.

📋 Table of Contents

If you work with data — even just a little — Excel formulas are your best friend. The right formula can turn hours of manual work into a 10-second task.

In this article, we cover the 10 most important Excel formulas every data analyst should know. Each formula includes a clear explanation, a real-world example, and a practical use case so you can start using it today.

Formula 01

📊 VLOOKUP — Look Up a Value in a Table

What it does: Searches for a value in the first column of a table and returns a value from another column in the same row.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Real Example: You have employee IDs in column A and you want to pull their salaries from a master table on another sheet.

=VLOOKUP(A2, MasterSheet!$A:$D, 4, FALSE)
This looks for the value in A2, searches the first column of the master table, and returns column 4 (salary). FALSE means exact match.

💡 Pro Tip: Always use FALSE for exact match. Use XLOOKUP instead of VLOOKUP in Excel 365 — it is more powerful and does not require the lookup column to be first.

Formula 02

🔍 XLOOKUP — The Modern Replacement for VLOOKUP

What it does: Searches a range for a value and returns a result. More flexible than VLOOKUP — can search any direction, handle errors, and return multiple columns.

=XLOOKUP(lookup_value, lookup_array, return_array, [not_found], [match_mode])

Real Example: Find a product's price from a product list.

=XLOOKUP(D2, ProductList!A:A, ProductList!C:C, "Not Found")
If the product is not found, it shows "Not Found" instead of an error.

💡 Pro Tip: XLOOKUP works left-to-right AND right-to-left. VLOOKUP can only go left-to-right. Available in Excel 365 and Excel 2021+.

Formula 03

📌 INDEX + MATCH — The Power Combo

What it does: INDEX returns a value at a position. MATCH finds the position. Together, they create a flexible lookup that works in any direction — even better than VLOOKUP.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Real Example: Find the salary of an employee by name, where names are not in the first column.

=INDEX(C2:C100, MATCH("Rahul", A2:A100, 0))
MATCH finds which row "Rahul" is in. INDEX returns the salary from that row in column C.

💡 Pro Tip: INDEX+MATCH is preferred over VLOOKUP in professional data work because it does not break when you insert or delete columns.

Formula 04

➕ SUMIF — Add Up Values That Meet a Condition

What it does: Adds values in a range based on a condition you define.

=SUMIF(range, criteria, sum_range)

Real Example: Total sales only for the "North" region.

=SUMIF(A2:A500, "North", C2:C500)
Column A has regions, Column C has sales amounts. This adds up all sales where region = North.

💡 Pro Tip: Use SUMIFS (with S) when you need multiple conditions: =SUMIFS(C2:C500, A2:A500, "North", B2:B500, "Q1") — North region AND Q1 only.

Formula 05

🔢 COUNTIF — Count Cells That Match a Condition

What it does: Counts the number of cells in a range that meet a specified condition.

=COUNTIF(range, criteria)

Real Example: Count how many employees are from Delhi.

=COUNTIF(B2:B200, "Delhi")

Count sales greater than 10,000:
=COUNTIF(C2:C200, ">10000")

💡 Pro Tip: COUNTIF with a wildcard: =COUNTIF(A2:A100, "Raj*") counts all names that start with "Raj" — Rajesh, Rajiv, Rajan, etc.

Formula 06

🧮 IF — Make Decisions in Your Data

What it does: Returns one value if a condition is TRUE and another if it is FALSE. It is the foundation of logic in Excel.

=IF(logical_test, value_if_true, value_if_false)

Real Example: Flag sales above target as "Hit" and below as "Miss".

=IF(C2>=50000, "Hit", "Miss")

Grade students: A, B, C, or Fail:
=IF(D2>=90,"A", IF(D2>=75,"B", IF(D2>=60,"C","Fail")))

💡 Pro Tip: Use IFS() in Excel 365 to avoid messy nested IFs: =IFS(D2>=90,"A", D2>=75,"B", D2>=60,"C", TRUE,"Fail")

Formula 07

🗓️ TEXT — Format Numbers and Dates as Text

What it does: Converts a value to text with a specific format. Extremely useful when combining dates or numbers with text.

=TEXT(value, format_text)

Real Example: Show a date in a readable format inside a sentence.

="Report for "&TEXT(A2,"DD-MMM-YYYY")
Result: "Report for 15-Jan-2026"

Format a number as currency with commas:
=TEXT(B2, "₹#,##0") → ₹1,25,000

💡 Pro Tip: Common format codes: "DD/MM/YYYY" for dates, "#,##0.00" for numbers, "0%" for percentages, "HH:MM AM/PM" for time.

Formula 08

🧹 TRIM + CLEAN — Remove Extra Spaces and Hidden Characters

What it does: TRIM removes leading, trailing, and extra spaces from text. CLEAN removes non-printable characters. Essential for cleaning messy data imported from other systems.

=TRIM(text)
=CLEAN(text)
=TRIM(CLEAN(text))

Real Example: Names imported from a database have extra spaces and hidden characters.

=TRIM(CLEAN(A2))
" Rahul Sharma " → "Rahul Sharma"

💡 Pro Tip: After TRIM and CLEAN, use PROPER() to fix inconsistent capitalisation: =PROPER(TRIM(CLEAN(A2))) → "rahul sharma" becomes "Rahul Sharma".

Formula 09

🔗 CONCATENATE / TEXTJOIN — Combine Text from Multiple Cells

What it does: Joins text from multiple cells into one. TEXTJOIN is the modern, better version that lets you add a separator.

=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
=A2&" "&B2  (using & operator)

Real Example: First name in A2, Last name in B2 — combine into full name.

=A2&" "&B2 → "Rahul Sharma"

Join city, state, and country with comma separator:
=TEXTJOIN(", ", TRUE, A2, B2, C2)
→ "Delhi, Delhi, India"

💡 Pro Tip: Use TEXTJOIN to join an entire column into one cell: =TEXTJOIN(", ", TRUE, A2:A50) — creates a comma-separated list from 50 cells.

Formula 10

🛡️ IFERROR — Handle Errors Gracefully

What it does: Wraps any formula and catches errors like #N/A, #DIV/0!, #VALUE!, and shows a custom message instead — keeping your reports clean and professional.

=IFERROR(value, value_if_error)

Real Example: VLOOKUP that shows "Not Found" instead of #N/A:

=IFERROR(VLOOKUP(A2, MasterTable, 3, FALSE), "Not Found")

Division that shows 0 instead of #DIV/0!:
=IFERROR(B2/C2, 0)

💡 Pro Tip: Always wrap VLOOKUP and XLOOKUP with IFERROR in production reports. A report with #N/A errors looks unprofessional and confuses stakeholders.

Quick Reference Table

Here is a quick summary of all 10 formulas. Click any formula name to jump to its full explanation:

#	Formula	Best Used For	Available In
1	VLOOKUP	Look up values in a table (left to right)	All Excel versions
2	XLOOKUP	Modern lookup — any direction, with fallback	Excel 365 / 2021+
3	INDEX+MATCH	Flexible lookup, works with any column order	All Excel versions
4	SUMIF / SUMIFS	Conditional totals	All Excel versions
5	COUNTIF / COUNTIFS	Count rows matching conditions	All Excel versions
6	IF / IFS	Conditional logic and classification	All / Excel 2019+
7	TEXT	Format numbers/dates as readable text	All Excel versions
8	TRIM + CLEAN	Clean messy imported data	All Excel versions
9	TEXTJOIN / &	Combine text from multiple cells	Excel 2019+ / All
10	IFERROR	Hide errors in professional reports	All Excel versions

🎯 Summary — Your Excel Formula Toolkit

Master these 10 formulas and you will be able to handle 80% of real-world data tasks in Excel:

VLOOKUP — Look up data from a master table
XLOOKUP — Modern, flexible, error-safe lookup
INDEX + MATCH — The most powerful lookup combination
SUMIF / SUMIFS — Conditional totals for reports
COUNTIF / COUNTIFS — Count data by conditions
IF / IFS — Logic and classification
TEXT — Format values for display
TRIM + CLEAN — Fix messy imported data
TEXTJOIN — Combine data intelligently
IFERROR — Keep reports clean and professional

Frequently Asked Questions

What are the most important Excel formulas for data analysts?+

The 10 most important formulas are VLOOKUP, XLOOKUP, INDEX+MATCH, SUMIF/SUMIFS, COUNTIF/COUNTIFS, IF/IFS, TEXT, TRIM+CLEAN, TEXTJOIN, and IFERROR. These cover 80% of real-world data tasks.

What is the difference between VLOOKUP and XLOOKUP?+

VLOOKUP can only search left-to-right and requires the lookup column to be first. XLOOKUP searches in any direction, returns multiple columns, and has built-in error handling. Available in Excel 365 and Excel 2021+.

When should I use INDEX MATCH instead of VLOOKUP?+

Use INDEX+MATCH when your lookup column is not the first column, or when you need to look left. INDEX+MATCH never breaks when columns are rearranged — unlike VLOOKUP.

What does IFERROR do in Excel?+

IFERROR catches errors like #N/A, #VALUE!, #REF!, #DIV/0! and replaces them with a value you specify. It keeps your reports clean and professional.

Is it necessary to learn all Excel formulas?+

No — focus on the core 10-15 most-used formulas. Master VLOOKUP/XLOOKUP, SUMIFS, COUNTIFS, IF, INDEX+MATCH, and IFERROR first. These alone handle the majority of real-world data work.

📚 Related Resources: Excel for Beginners course | Advanced Excel Formulas | Data Cleaning in Excel | Pivot Tables vs Power Query

Want to learn Excel from scratch?

Our free Excel for Beginners course covers all these formulas step-by-step with practice tasks and real-world projects.

Start Free Course → Get Free Templates

Top 10 Excel Formulas EveryData Analyst Must Know