| Formula | Use |
|---|---|
=VLOOKUP(val,range,col,0) | Look rightward, exact match (0) |
=HLOOKUP(val,range,row,0) | Look downward in horizontal table |
=INDEX(range,row,col) | Return value at row,col of range |
=MATCH(val,range,0) | Return position of val in range |
=INDEX(D,MATCH(val,A,0)) | Look left — more powerful than VLOOKUP |
=XLOOKUP(val,lookup,return) | Any direction, handles not-found gracefully |
=CHOOSE(n,v1,v2,v3) | Return nth value from list |
=INDIRECT("A"&ROW()) | Dynamic cell reference from string |
| Formula | Use |
|---|---|
=IF(cond,yes,no) | Basic conditional |
=IFS(c1,v1,c2,v2,...) | Multiple conditions (no nesting) |
=AND(c1,c2) | All conditions true |
=OR(c1,c2) | Any condition true |
=NOT(condition) | Reverse true/false |
=IFERROR(formula,val) | Return val if formula errors |
=IFNA(formula,val) | Return val only for #N/A errors |
=SWITCH(val,c1,r1,c2,r2,def) | Match value, return result |
| Formula | What it does |
|---|---|
=SUM(range) | Sum all values |
=SUMIF(range,"crit",sum_range) | Sum if single condition met |
=SUMIFS(sum,r1,c1,r2,c2) | Sum with multiple AND conditions |
=COUNTIF(range,"criteria") | Count matching cells |
=COUNTIFS(r1,c1,r2,c2) | Count with multiple conditions |
=AVERAGEIF(r,"crit",avg_r) | Average if condition met |
=MAXIFS(max_r,r1,c1) | Max with conditions |
=MINIFS(min_r,r1,c1) | Min with conditions |
=SUBTOTAL(9,range) | Sum excluding hidden rows (9=SUM) |
=AGGREGATE(func,opt,range) | Aggregation ignoring errors |
| Formula | Use |
|---|---|
=CONCATENATE(a,b) / =A&" "&B | Join strings |
=TEXTJOIN(",",1,range) | Join range with delimiter |
=LEFT(text,n) | First n characters |
=RIGHT(text,n) | Last n characters |
=MID(text,start,len) | Extract n chars from position |
=LEN(text) | String length |
=TRIM(text) | Remove extra spaces |
=CLEAN(text) | Remove non-printable chars |
=SUBSTITUTE(t,old,new) | Replace all occurrences |
=FIND("x",text) | Position of char (case-sensitive) |
=TEXT(val,"format") | Format number as text "₹#,##0.00" |
=VALUE(text) | Convert text to number |
| Formula | Returns |
|---|---|
=TODAY() | Today's date |
=NOW() | Current date+time |
=DATE(y,m,d) | Build date from parts |
=YEAR/MONTH/DAY(date) | Extract year/month/day |
=DATEDIF(start,end,"D") | Days between dates (D/M/Y) |
=NETWORKDAYS(s,e,holidays) | Working days between dates |
=EDATE(date,months) | Date n months forward/back |
=WEEKDAY(date,2) | Day of week (2=Mon=1) |
=EOMONTH(date,0) | Last day of current month |
| Formula | Use |
|---|---|
=UNIQUE(range) | Return unique values |
=SORT(range,col,1) | Sort range (1=ASC, -1=DESC) |
=SORTBY(range,by_range,-1) | Sort by another range |
=FILTER(range,cond,"not found") | Filter rows matching condition |
=SEQUENCE(rows,cols,start,step) | Generate number sequence |
=RANDARRAY(rows,cols) | Random numbers in array |
=XLOOKUP(val,l,r,,-1) | Approximate match (sorted) |