Excel Formula Guide

Choose the appropriate function and explore 30 practical formula recipes.

Employee ID → employee name

=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not Found")

Loan ID → outstanding POS

=XLOOKUP(G2,$A$2:$A$500,$D$2:$D$500,"Not Found")

Flexible lookup for older workbooks

=INDEX($C$2:$C$100,MATCH(E2,$A$2:$A$100,0))

Sum with branch + status criteria

=SUMIFS($D$2:$D$500,$B$2:$B$500,H2,$C$2:$C$500,"Active")

Count active overdue cases

=COUNTIFS($C$2:$C$500,"Active",$E$2:$E$500,">90")

Average by branch and status

=AVERAGEIFS($D$2:$D$500,$B$2:$B$500,H2,$C$2:$C$500,"Active")

Highest POS in an overdue segment

=MAXIFS($D$2:$D$500,$E$2:$E$500,">90",$C$2:$C$500,"Active")

Target achievement label

=IF(B2>=C2,"Achieved","Review")

Create ordered risk bands

=IFS(E2>180,"Critical",E2>90,"High",E2>30,"Watch",TRUE,"Current")

Prevent divide-by-zero errors

=IFERROR(B2/C2,0)

Clean imported text

=TRIM(CLEAN(A2))

Extract an email domain

=TEXTAFTER(A2,"@")

Join nonblank notes into one cell

=TEXTJOIN(" | ",TRUE,B2:E2)

Get the month-end date

=EOMONTH(A2,0)

Calculate working-day turnaround time

=NETWORKDAYS(A2,B2,$H$2:$H$20)

Calculate completed years between dates

=DATEDIF(A2,TODAY(),"Y")

Return only active records

=FILTER(A2:F500,F2:F500="Active","No rows")

Create a sorted unique branch list

=SORT(UNIQUE(B2:B500))

Return the top 5 records by value

=TAKE(SORTBY(A2:D500,D2:D500,-1),5)

Stack monthly tables vertically

=VSTACK(JanData,FebData,MarData)

Return only reporting columns

=CHOOSECOLS(A2:H500,1,2,5,8)

Calculate a weighted average

=SUMPRODUCT(B2:B100,C2:C100)/SUM(C2:C100)

Sum only visible filtered rows

=SUBTOTAL(9,D2:D500)

Summarize while ignoring errors

=AGGREGATE(9,6,D2:D500)

Make a long formula readable with LET

=LET(pos,D2,dpd,E2,IF(dpd>90,pos*0.25,pos*0.1))

Calculate monthly EMI

=-PMT(B2/12,C2*12,A2)

Calculate net present value

=NPV(B2,C3:C7)+C2

Estimate project IRR

=IRR(C2:C7)

Count missing required values

=COUNTBLANK(B2:B500)

Flag duplicate IDs

=IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","OK")

Interactive practice runs in your browser.