Advanced Excel Formulas Explained: XLOOKUP, INDEX MATCH, SUMIFS, COUNTIFS, IFERROR, TEXT, Dates, Dynamic Arrays and Pivot Tables with Indian Examples

Once you are comfortable with SUM, AVERAGE and IF, a few advanced functions unlock most professional spreadsheet work: finding data in other tables, summing by conditions, cleaning text, working with dates and summarising thousands of rows in seconds. This guide explains each with realistic examples from Indian business and personal finance.

Some functions (XLOOKUP, FILTER, UNIQUE, SORT, LET) require Microsoft 365 or Excel 2021 and later. Older versions need alternatives, which are noted below.

1. Lookup functions

Suppose sheet Products lists product codes in column A, names in B and prices in C, and your sales sheet has a product code in A2.

• XLOOKUP: =XLOOKUP(A2,Products!A:A,Products!C:C,"Not found") returns the price, looks in any direction, handles missing values and defaults to exact match.
• VLOOKUP: =VLOOKUP(A2,Products!A:C,3,FALSE) works in all versions, but the lookup value must be in the first column and the column number breaks if columns are inserted. Always use FALSE for exact match.
• INDEX and MATCH: =INDEX(Products!C:C,MATCH(A2,Products!A:A,0)) is flexible and works everywhere.

2. Conditional sums and counts

• SUMIF: =SUMIF(B:B,"Mumbai",D:D) sums sales for Mumbai.
• SUMIFS: =SUMIFS(D:D,B:B,"Mumbai",C:C,"Q2") uses multiple conditions.
• COUNTIFS: =COUNTIFS(B:B,"Delhi",E:E,">=50000") counts rows meeting all conditions.
• AVERAGEIFS averages with conditions.
• Use wildcards: "A*" matches text starting with A. Use criteria like "<>" for not blank.

3. Logical functions

• IFS: =IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"D") replaces long nested IF chains.
• AND, OR, NOT combine tests: =IF(AND(B2>=40,C2>=40),"Pass","Fail").
• IFERROR: =IFERROR(B2/C2,0) replaces errors. Use it after you understand why the error occurs, so you do not hide real problems.
• SWITCH maps values to results, such as state codes to names.

4. Text functions

• TRIM removes extra spaces, CLEAN removes non-printing characters, PROPER, UPPER, LOWER change case.
• LEFT, RIGHT, MID extract parts of text. Example: the first two digits of a GSTIN give the state code: =LEFT(A2,2). See GSTIN verification.
• CONCAT / TEXTJOIN join text with a separator: =TEXTJOIN(", ",TRUE,A2:C2).
• TEXT formats numbers or dates as text: =TEXT(A2,"dd-mmm-yyyy").
• FIND, SEARCH, SUBSTITUTE locate or replace text. Flash Fill (Ctrl+E) can also split names quickly.
• TEXTSPLIT (365) splits text into columns by a delimiter.

5. Date and time functions

• TODAY, NOW for current date and time.
• DATEDIF: =DATEDIF(A2,TODAY(),"Y") gives completed years, useful for age or service.
• EDATE: =EDATE(A2,6) adds six months, useful for due dates.
• EOMONTH returns the month end. NETWORKDAYS counts working days between dates, excluding weekends and optional holidays.
• For Indian financial years (April to March), use a helper formula such as =IF(MONTH(A2)>=4,YEAR(A2),YEAR(A2)-1) to get the starting year of the FY.

6. Finance functions

• PMT: =-PMT(8.5%/12,20*12,5000000) gives the monthly EMI for a ₹50 lakh loan at 8.5 percent over 20 years. Verify with the EMI calculator.
• FV: future value of regular investments. Check against the SIP calculator and future value calculator.
• RATE, NPV, IRR: rate of return and discounting.
• XIRR: =XIRR(values,dates) for irregular cash flows such as SIPs. See XIRR guide and the XIRR calculator.
• CAGR can be computed as =(End/Start)^(1/Years)-1. See the CAGR calculator.

7. Dynamic arrays (Microsoft 365)

• FILTER: =FILTER(A2:E100,B2:B100="Pune") returns all rows for Pune and spills automatically.
• UNIQUE: list distinct customers or cities.
• SORT and SORTBY sort results with formulas.
• SEQUENCE generates number lists. LET names intermediate calculations to make long formulas readable.

8. Pivot tables

1. Convert your data into a table (Ctrl+T) with clean headers.
2. Insert > PivotTable and choose a new sheet.
3. Drag fields: month into Rows, region into Columns, sales into Values.
4. Use slicers to filter interactively and group dates by months or quarters.
5. Refresh the pivot when data changes (Alt+F5).

Pivot tables answer questions like "which product earned the most in each state" in seconds. Combine with profit margin calculations for profitability views, and see small business accounting for the records to analyse.

Best practices

• Keep raw data on one sheet and calculations on another. Never overwrite source data.
• Use structured table references and named ranges to make formulas readable.
• Avoid volatile functions in huge files, and avoid whole-column references in very large sheets if the file becomes slow.
• Document assumptions in a notes sheet.
• Audit formulas with Formulas > Trace Precedents and Evaluate Formula.
• Protect cells with formulas, and keep backups. See cloud storage comparison.

New to Excel? Start with Excel tips for beginners. To use these skills professionally, see how to write a resume and list your spreadsheet skills with specific examples.

Worked example: a simple sales dashboard

Imagine a table named Sales with columns Date, City, Product, Units, Price and Amount. Put Amount as =[@Units]*[@Price]. Then build a summary sheet: a UNIQUE list of cities, next to it =SUMIFS(Sales[Amount],Sales[City],A2) for total sales, and =COUNTIFS(Sales[City],A2) for the number of orders. Add average order value as total divided by orders, wrapped in IFERROR for cities with no orders. A pivot chart on the same table gives a month-wise trend, and a slicer for Product lets a manager explore without touching formulas. If a city name appears with different spellings, clean it first with TRIM and PROPER, or the totals will split across near-duplicates.

Debugging checklist

1. #N/A usually means a lookup value was not found; check spaces and text versus number mismatches.
2. #VALUE! often means text inside a calculation; check for stray characters.
3. #REF! means a referenced cell or column was deleted.
4. Wrong totals frequently come from numbers stored as text; use the VALUE function or Text to Columns.
5. Press F9 on part of a formula in the formula bar (then Esc) to see what that part returns.

Frequently asked questions

Is XLOOKUP better than VLOOKUP?

Generally yes, as it can look left, defaults to exact match and handles missing values, but needs a newer Excel version.

What is SUMIFS used for?

Summing values that meet multiple conditions, such as sales by city and quarter.

How do I calculate EMI in Excel?

Use the PMT function with monthly rate, number of months and loan amount.

What does IFERROR do?

It replaces an error result with a value you choose.

When should I use a pivot table?

To summarise large datasets by categories quickly.

Which functions need Microsoft 365?

XLOOKUP, FILTER, UNIQUE, SORT and LET need a recent version.

Last reviewed 8 November 2026. Rates, limits and rules change, so confirm figures with the official source before you act.