=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),0),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Text

Remove Duplicate Words

Remove repeated words from a text string

=TEXTJOIN(" ",TRUE,IF(MATCH(TEXTSPLIT(A2," "),TEXTSPLIT(A2," "),0)=SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1),TEXTSPLIT(A2," "),""))

Text

Split Full Name into First/Last

Separate a full name into first name and last name

=LEFT(A2, FIND(" ", A2)-1)

Text

Count Words in Cell

Count the number of words in a text cell

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

Text

Advanced Find & Replace

Find and replace text within cells using formulas

=SUBSTITUTE(A2, "old", "new")

Text

Basic Email Validation

Check if a text string is a valid email format

=AND(ISERROR(FIND(" ",A2)),LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1,FIND("@",A2)>1,FIND(".",A2,FIND("@",A2))>FIND("@",A2)+1)

Text

Join Cells with Separator

Combine multiple cells into one with a custom separator

=TEXTJOIN(", ", TRUE, A2:D2)

Lookup

Fuzzy/Approximate Match

Find the closest text match in a list

=INDEX(B2:B100,MATCH(MIN(MMULT((CODE(MID(UPPER(A2),SEQUENCE(LEN(A2)),1))-CODE(MID(UPPER(B2),SEQUENCE(LEN(B2)),1)))^2,SEQUENCE(LEN(A2),1,1,0))),MMULT((CODE(MID(UPPER(A2),SEQUENCE(LEN(A2)),1))-CODE(MID(UPPER(B2),SEQUENCE(LEN(B2)),1)))^2,SEQUENCE(LEN(A2),1,1,0)),0))

Lookup

Multi-Criteria Lookup

Look up values using multiple conditions

=INDEX(D2:D100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

Lookup

Cascading Dropdown Values

Create dependent dropdown lists where options change based on prior selection

=INDIRECT(A2)

Lookup

Reverse VLOOKUP (Right to Left)

Look up values from right to left (what VLOOKUP can't do)

=INDEX(A2:A100,MATCH(D2,C2:C100,0))

Lookup

Two-Way Lookup (INDEX MATCH MATCH)

Look up a value at the intersection of a row and column

=INDEX(B2:F10, MATCH(H2,A2:A10,0), MATCH(I2,B1:F1,0))

Lookup

Return Multiple Results from Lookup

Return all matching values, not just the first one

=FILTER(B2:B100, A2:A100=D2)

Lookup

Find Closest Numeric Match

Find the nearest number in a list to your target value

=INDEX(A2:A100, MATCH(MIN(ABS(A2:A100-C2)), ABS(A2:A100-C2), 0))

Lookup

Lookup with Conditions

Combine IF logic with VLOOKUP or INDEX/MATCH

=IF(C2="Excel", VLOOKUP(A2, ExcelData, 2, FALSE), VLOOKUP(A2, SheetsData, 2, FALSE))

Lookup

Cross-Sheet Reference Lookup

Look up values from another sheet or workbook

=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)

Lookup

Dynamic Named Range

Create ranges that automatically expand as data grows

=OFFSET(A1, 0, 0, COUNTA(A:A), 1)

Statistical

Moving Average (Rolling Average)

Calculate a rolling average over N periods

=AVERAGE(OFFSET(A2,-B$1+1,0,B$1,1))

Statistical

Calculate Percentile

Find the value at a specific percentile in your data

=PERCENTILE(A2:A100, 0.9)

Statistical

Detect Outliers (IQR Method)

Identify outlier values using the Interquartile Range method

=IF(OR(A2<QUARTILE($A$2:$A$100,1)-1.5*(QUARTILE($A$2:$A$100,3)-QUARTILE($A$2:$A$100,1)), A2>QUARTILE($A$2:$A$100,3)+1.5*(QUARTILE($A$2:$A$100,3)-QUARTILE($A$2:$A$100,1))), "Outlier", "Normal")

Statistical