LET and LAMBDA: Write Your Own Excel Functions (No VBA)
Use LET to name parts of long formulas and LAMBDA to create reusable custom functions like CLEANTEXT or FYEAR — saved in Name Manager,…
Read article →Category Archives: Formula Combos
Unusual formula combinations that turn hours of manual work into one cell.
Find the LAST Match in a List: the LOOKUP(2, 1/…) Trick Explained
Find the LAST Match in a List: the LOOKUP(2, 1/…) Trick Explained
Get the latest price, last status or most recent date for an item with the famous LOOKUP(2,1/(range=value)) formula — explained step by step, plus…
Read article →SUMPRODUCT: The Excel Formula That Does (Almost) Everything
SUMPRODUCT: The Excel Formula That Does (Almost) Everything
SUMPRODUCT multiplies arrays and adds the result — which makes it a multi-condition counter, a weighted average, a case-sensitive lookup and more, in every…
Read article →XLOOKUP with Two (or More) Criteria — Plus the INDEX-MATCH Version
XLOOKUP with Two (or More) Criteria — Plus the INDEX-MATCH Version
Look up a value using two conditions — product AND region, or employee AND month — with XLOOKUP, INDEX-MATCH, or a helper column. With…
Read article →Return ALL Matches in One Cell: TEXTJOIN + FILTER (the VLOOKUP Upgrade)
Return ALL Matches in One Cell: TEXTJOIN + FILTER (the VLOOKUP Upgrade)
VLOOKUP returns only the first match. Combine TEXTJOIN, UNIQUE and FILTER to list every matching value in one cell — plus a version for…
Read article →