Should I use VLOOKUP or XLOOKUP?

Use XLOOKUP if you have Excel 2021 or Microsoft 365—it's more powerful, can search in any direction, and has built-in error handling. Use VLOOKUP only for compatibility with older Excel versions or when sharing files with users who don't have XLOOKUP.

What is the difference between SUMIF and SUMIFS?

SUMIF sums cells based on a single condition, while SUMIFS can sum based on multiple conditions. SUMIFS also has a different argument order—the sum range comes first. For new formulas, SUMIFS is recommended as it's more flexible.

When should I use INDEX/MATCH instead of VLOOKUP?

Use INDEX/MATCH when you need to look up values to the left of your search column, when working with large datasets (it's faster), or when you might insert/delete columns. INDEX/MATCH is also more flexible for two-way lookups.

Is CONCATENATE or TEXTJOIN better for combining text?

TEXTJOIN is better in most cases—it lets you specify a delimiter and can ignore empty cells. CONCATENATE is older and requires manually adding delimiters between each argument. Use TEXTJOIN for Excel 2019+ or Microsoft 365.

What is the difference between COUNT, COUNTA, and COUNTIF?

COUNT counts only cells with numbers. COUNTA counts all non-empty cells (numbers, text, errors). COUNTIF counts cells that meet a specific criterion you define. Use COUNTBLANK to count empty cells.

What should I practice after reading this tutorial?

Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.

Which Excel version do I need for the formulas in this article?

Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.

Should I still learn VLOOKUP in 2026?

Yes for legacy files and teams on older Excel. Learn XLOOKUP as well if you use Microsoft 365 — many new models start there.

Tutorials - 2026-01-21

Excel Functions Compared: When to Use Each One

Stop guessing which Excel function to use. This comprehensive comparison guide helps you choose the right function for every situation.

Why Function Comparisons Matter

Excel has over 500 functions, and many do similar things. This guide compares the most commonly confused functions. For a complete function reference, see our Excel Functions: Complete Guide.

Lookup Functions

VLOOKUP vs XLOOKUP vs INDEX/MATCH Feature VLOOKUP XLOOKUP INDEX/MATCH Search direction Right only Any Any Default match Approximate Exact Configurable Error handling Manual Built-in Manual Excel version All 2021/365 All Quick Decision: XLOOKUP — Best for Excel 2021/365 INDEX/MATCH — Best for older versions VLOOKUP — Simple legacy lookups Full comparison →

Counting Functions

COUNTIF vs COUNTIFS Function Conditions COUNTIF 1 COUNTIFS Multiple Full comparison →

Summing Functions

SUM vs SUMIF vs SUMIFS Function Conditions SUM None SUMIF 1 SUMIFS Multiple Full comparison →

Conditional Logic

IF vs IFS Function Best for IF 1-3 conditions IFS Multiple conditions Full comparison →

Text Functions

LEFT vs MID vs RIGHT Function Extracts from LEFT Beginning RIGHT End MID Any position Full comparison → TEXT vs TEXTJOIN Function Purpose TEXT Format numbers TEXTJOIN Combine text Full comparison →

COUNTIF vs COUNTIFS SUM vs SUMIF vs SUMIFS VLOOKUP vs XLOOKUP vs INDEX/MATCH IF vs IFS LEFT vs MID vs RIGHT TEXT vs TEXTJOIN

Apply this tutorial in your workbook

Copy one example from this article into a blank sheet. Change the sample ranges to match your column letters. Press F2 and Enter after edits so Excel recalculates. Compare your result to the expected output in the article. Related hubs: Excel functions directory · Formula guides · Function comparisons \nLookup help: VLOOKUP function · VLOOKUP tutorial · Fix #N/A · vs XLOOKUP \nLookup help: XLOOKUP function · XLOOKUP tutorial · vs VLOOKUP

Frequently asked questions

Should I use VLOOKUP or XLOOKUP? Use XLOOKUP if you have Excel 2021 or Microsoft 365—it's more powerful, can search in any direction, and has built-in error handling. Use VLOOKUP only for compatibility with older Excel versions or when sharing files with users who don't have XLOOKUP.
What is the difference between SUMIF and SUMIFS? SUMIF sums cells based on a single condition, while SUMIFS can sum based on multiple conditions. SUMIFS also has a different argument order—the sum range comes first. For new formulas, SUMIFS is recommended as it's more flexible.
When should I use INDEX/MATCH instead of VLOOKUP? Use INDEX/MATCH when you need to look up values to the left of your search column, when working with large datasets (it's faster), or when you might insert/delete columns. INDEX/MATCH is also more flexible for two-way lookups.
Is CONCATENATE or TEXTJOIN better for combining text? TEXTJOIN is better in most cases—it lets you specify a delimiter and can ignore empty cells. CONCATENATE is older and requires manually adding delimiters between each argument. Use TEXTJOIN for Excel 2019+ or Microsoft 365.
What is the difference between COUNT, COUNTA, and COUNTIF? COUNT counts only cells with numbers. COUNTA counts all non-empty cells (numbers, text, errors). COUNTIF counts cells that meet a specific criterion you define. Use COUNTBLANK to count empty cells.
What should I practice after reading this tutorial? Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.
Which Excel version do I need for the formulas in this article? Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.
Should I still learn VLOOKUP in 2026? Yes for legacy files and teams on older Excel. Learn XLOOKUP as well if you use Microsoft 365 — many new models start there.