Excel formulas every professional should know: XLOOKUP, SUMIFS

Fifteen Excel formulas cover the most common spreadsheet jobs: lookup (XLOOKUP, VLOOKUP, INDEX with MATCH), conditional sums and counts (SUMIFS, COUNTIFS), logic (IF, IFS, IFERROR), text (TEXTJOIN, TRIM), dates (EOMONTH, NETWORKDAYS) and dynamic arrays (FILTER, UNIQUE, LET). XLOOKUP, FILTER, UNIQUE and LET need Microsoft 365, Excel 2021 or Excel 2024. IFS and TEXTJOIN need Excel 2019 or later.

Which Excel formulas should every professional know?

The table below groups the fifteen formulas by the job they do, counting INDEX with MATCH as one because the two work as a pair. It shows where each exists, according to the Applies To lists on Microsoft Support's function pages as of 7 November 2026.

Every example below uses one hypothetical sales table, held in cells A1 to E9 of a worksheet. The rows are invented.

What is the difference between VLOOKUP and XLOOKUP?

XLOOKUP searches one range and returns the matching item from another range, while VLOOKUP searches the first column of a table and returns a value from a column number. Microsoft's function pages show three differences.

• XLOOKUP gives an exact match by default, while VLOOKUP defaults to an approximate match that assumes the first column is sorted.
• XLOOKUP can return a column on either side of the search column, while VLOOKUP's lookup value must sit in the first column of its table.
• XLOOKUP takes the return range as its own argument, while VLOOKUP counts a column number inside the table.

That last difference matters after a table is edited, because a fixed column number can point at the wrong column or return a #REF! error. INDEX with MATCH can do the same job as XLOOKUP and is listed from Excel 2016, so it suits files shared with people on older versions.

MATCH returns a position, which is 5 for ORD-1005 here, and INDEX returns the value at that position. A missing key gives a #N/A error from VLOOKUP and MATCH, and gives the text in the fourth argument from XLOOKUP when one is supplied.

What does SUMIFS do?

SUMIFS adds the numbers in a range that meet every condition, and COUNTIFS counts the rows that meet every condition. SUMIFS takes the range to add first and then pairs of a criteria range and a criterion, with up to 127 pairs. COUNTIFS takes only the pairs, also up to 127, and every range must be the same size. Criteria can be a number, an expression such as ">20000", a cell reference or text in quotation marks, and the question-mark and asterisk wildcards work in text criteria.

The SUMIFS result adds the three South orders above Rs 20,000 (52000, 21000 and 24500). A founder tallying customer-interview answers or landing-page sign-ups can use COUNTIFS the same way; the method for those tests is in the guide on validating a startup idea.

Which logic formulas handle if-then rules and errors?

IF returns one value when a test is true and another when it is false. IFS tests several conditions in order and returns the value for the first true one, so it replaces a chain of nested IF formulas. It accepts up to 127 conditions, and a final TRUE test works as the default. If no condition is true, IFS returns a #N/A error. IFERROR returns a value of your choice when a formula produces an error, and otherwise returns the formula's result.

IFERROR catches seven error types: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. That includes #NAME?, which usually means a function name was misspelt, so IFERROR can hide a real mistake. XLOOKUP's fourth argument is a narrower way to handle a missing key.

Which text and date formulas clean up reports?

TRIM removes extra spaces from text, leaving single spaces between words. Microsoft warns that it removes only the ordinary space (character 32), so the non-breaking space (character 160) common in text copied from web pages stays. TEXTJOIN joins text from a range with a separator and can skip empty cells. EOMONTH returns the last day of a month a set number of months away, and NETWORKDAYS counts working days between two dates after removing weekends and any listed holidays. Dates should be built with DATE or come from other formulas, because text dates cause problems.

Without the holiday, the NETWORKDAYS example returns 21 working days. NETWORKDAYS.INTL lets the weekend days be chosen, which matters for a workplace that does not follow the default weekend.

What do FILTER, UNIQUE and LET do?

FILTER returns every row that meets a condition, UNIQUE returns each distinct value once, and LET gives names to intermediate results so a long formula is shorter and each part calculates once. All three are dynamic array features: results spill into neighbouring cells, and among desktop versions all three are listed only for Microsoft 365, Excel 2021 and Excel 2024, not Excel 2019 or 2016.

Without the third FILTER argument, an empty result gives a #CALC error. Conditions combine with * for AND and + for OR. The LET example averages the four South amounts, which sum to 117000.

What are the limits of these formulas?

A formula is only as reliable as the table under it. VLOOKUP's approximate default assumes a sorted first column and can return a wrong row from an unsorted table unless the last argument is FALSE. Text criteria without quotation marks give 0 in SUMIFS. Dynamic array formulas have limited support between workbooks, and a linked formula returns a #REF error when refreshed with the source workbook closed.

Microsoft's own note adds that a workbook built in a newer version can arrive with XLOOKUP in it, and the function is not available in Excel 2016 or 2019.

The list above is a selection by job, not a popularity count of the formulas used most at work. The evidence on Excel in jobs is narrower. The US Department of Labor's O*NET lists Microsoft Excel among the hot technologies for financial and investment analysts, and CXOToday, reporting a NASSCOM and Indeed study, lists Microsoft Excel among in-demand internship skills in India. An article on financial analyst skills in demand places spreadsheet skills alongside the rest of that toolkit.

Where does Excel fit in a data analytics programme?

The Certification Program in Data Analytics with AI and Gen AI, E&ICT Academy IIT Roorkee is an online programme from E&ICT Academy, IIT Roorkee, run with Masai as the implementation partner, with live sessions and an optional campus immersion, covering SQL, Python, Google Sheets, prompt engineering, LLM tools and agentic workflows with LangChain and CrewAI, and a capstone project. Its listed topics include advanced Google Sheets functions alongside SQL, Python and Pandas.

A three-statement financial model is a good practice project, since it is built almost entirely from the formulas above.

Frequently asked questions

What is the difference between VLOOKUP and XLOOKUP?

XLOOKUP takes a search range and a separate return range, defaults to an exact match and can return from either side of the search column. VLOOKUP searches the first column of a table, returns a value by column number and defaults to an approximate match. XLOOKUP is not in Excel 2016 or 2019.

Which Excel formulas are most used in jobs?

Any such list is a selection, not a measured ranking of formula use. O*NET lists Microsoft Excel as a hot technology for financial and investment analysts, and CXOToday reports Microsoft Excel among in-demand internship skills in India. Lookup, SUMIFS, COUNTIFS and IF make a sensible starting group.

What does SUMIFS do?

SUMIFS adds the values in one range for every row where all the stated conditions are true. It takes the range to add, then pairs of a criteria range and a criterion, with up to 127 pairs. For example, it can add all South orders above Rs 20,000.

Is XLOOKUP available in Excel 2019?

No. Microsoft's XLOOKUP page says the function is not available in Excel 2016 and Excel 2019. It is in Microsoft 365, Excel 2021 and Excel 2024. Excel 2019 does include IFS and TEXTJOIN, but not FILTER, UNIQUE or LET, which are listed for Microsoft 365, Excel 2021 and Excel 2024.

Can INDEX with MATCH replace VLOOKUP when XLOOKUP is missing?

Where XLOOKUP is not available, yes. INDEX with MATCH can look to the left, does not depend on a fixed column number and works in Excel 2016. Where everyone uses Microsoft 365, Excel 2021 or Excel 2024, XLOOKUP does the same job in a single function with an exact-match default.

Why does IFERROR hide mistakes?

IFERROR returns the replacement value for seven error types, including #NAME? from a misspelt function and #REF! from a deleted column. A broken formula and an expected blank therefore look the same. It is safest around a calculation where an error is known and acceptable, such as a division by zero.

Next step

Type the sales table into a worksheet, enter each example and check that the result matches the table. For analysts who want to move from spreadsheets to SQL, Python and AI-assisted analysis, the Certification Program in Data Analytics with AI and Gen AI, E&ICT Academy IIT Roorkee page lists the modules.