Excel Help Sheet
Lookup & Reference
- XLOOKUP:
=XLOOKUP(A2, lookup_range, return_range, "Not found") - INDEX + MATCH:
=INDEX(return_range, MATCH(A2, lookup_range, 0))
Logic & Error Handling
- IF:
=IF(A2>=90,"Approved","Denied") - IFS:
=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"D") - IFERROR:
=IFERROR(your_formula,"") - AND / OR:
=IF(AND(A2>0,B2="Y"),"OK","Check")
Text Cleanup (super useful)
- TRIM: remove extra spaces:
=TRIM(A2) - SUBSTITUTE: replace text:
=SUBSTITUTE(A2,"-","") - TEXTAFTER / TEXTBEFORE: split text around a delimiter (newer Excel):
=TEXTBEFORE(A2," ")/=TEXTAFTER(A2," ") - LEFT / RIGHT / MID:
=LEFT(A2,5)/=RIGHT(A2,4)/=MID(A2,3,6)
Dates
- TODAY:
=TODAY() - EOMONTH: end of month:
=EOMONTH(A2,0) - WORKDAY: add business days:
=WORKDAY(A2,5) - NETWORKDAYS: business days between dates:
=NETWORKDAYS(A2,B2)
Dynamic Lists (great for reporting)
- FILTER:
=FILTER(A2:D100, D2:D100="Approved") - SORT:
=SORT(A2:D100, 4, -1) - UNIQUE:
=UNIQUE(B2:B100)
Paste Data Into Multiple Fields Tip
If software supports it, you can often paste values separated by a tab so each value lands in the next field. In Excel, you can build a tab-separated line like this:
=A2 & CHAR(9) & B2 & CHAR(9) & C2
Then copy that cell and paste into the first field—tabs may advance across fields.