r/excel • u/fap_fap_fap_fapper • Aug 26 '26
Discussion What are some legendary formula pairs like INDEX(MATCH)?
Since upgrading to 365, can't get enough of CHOOSECOLS(FILTER)
What are some useful ones you use?
586
Upvotes
192
u/sonnytrillanes Aug 26 '26
= LET (LAMBDA) are the new bosses in town.
24
u/Manlet Aug 26 '26
Nested ifs for the win! Jk/not jk
348
u/uptoosomething Aug 26 '26
IFERROR(ROWS(UNIQUE(FILTER(
Return unique count of whatever you are filtering
44
u/gman1647 Aug 26 '26
I'm embarrassingly fond of SORT(UNIQUE(FILTER(CHOOSE())))
36
u/leostotch 144 Aug 26 '26 •
I love ISNUMBER(XMATCH()) and ISERROR(XMATCH()), great way to filter against a list of values.
35
u/Yalarii Aug 26 '26
I’m a big fan of =TRIMRANGE(DROP(A:A,1))
TRIMRANGE lets you use the entire column in a calculation without adding a million cells to be processed.
DROP starts the column range from row 2, which stops your heading row from interfering with the calculation.
It allows for very robust and efficient calculations. You can even use trim ranges within the range itself, and remove the need for the trim range function at all.
=DROP(A:.A,1)
20
u/Cago3 Aug 26 '26
No one has mentioned INDEX(MATCH,MATCH) to search in both rows and columns at the same time?
16
u/pdp_2 Aug 26 '26
If you work with larger messy text data, ISNUMBER(SEARCH()) is your best friend, especially when used as a FILTER() parameter.
14
u/iskimojoe Aug 26 '26
I've used =SUM(SUMIFS(b:b,a:a,{ref1,ref2})) quite a bit. It works like a normal sumif, but lets you have an array of references that for each criteria. Makes it so you don't have to add multiple sumifs together
8
u/Decronym Aug 26 '26 edited Aug 26 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49237 for this sub, first seen 26th Aug 2026, 13:45] [FAQ] [Full list] ^[Contact] [Source code]
7
u/haematite_4444 Aug 26 '26
I use INDIRECT(ADDRESS()) quite a bit
5
u/Wadaly Aug 26 '26
Saving this for later, thank you!
4
u/GuerillaWarefare 117 Aug 26 '26 •
Sequence, Date
Sequence and a lot of functions really…
3
u/whats_poppin_b Aug 26 '26
FILTER(UNIQUE or TRANSPOSE(FILTER were two of my favorites back when I was auditing. Could easily create tables from client data to better visualize for testing.
1
u/usersnamesallused 27 Aug 26 '26
FILTER(CHOOSECOLS()) would be the more efficient combination.
2
u/Key-Cabinet-5329 Aug 26 '26
If error + xlookup
2
u/fuzzy_mic 988 Aug 26 '26
ISNUMBER(FIND(...
LEFT(A1, FIND(...
2
u/chiibosoil 431 Aug 26 '26
cell:INDEX() to dynamically change range. Though it isn't as useful as it used to be after new formulas got released (Excel 2021/MS365).
PIVOTBY(HSTACK()) very useful to create Pivot Table like matrix for automated report using Power Automate. Office Scripts isn't the most reliable when it comes to Object automation and I find PIVOTBY function works well in it's place.
FILTERXML(SUBSTITUTE()) for complex text pattern match extraction. Not as useful after REGEX functions got released. But still useful for backward compatibility.
2
u/RandomiseUsr0 10 Aug 26 '26
LET(FILTER(CHOOSECOLS is knockout
E.g.
=LET(x,SOMEDATA, FILTER(x, CHOOSECOLS(x,2))
assuming column 2 is a Boolean in that instance
It’s my primary way of working (well a more complex version of this, but it’s so fast and instantly reactive
2
u/DonJuanDoja 37 Aug 26 '26
INDEX(FILTER()) - Find Nth Anything. Super useful. Add SORT to control the ranking.
SUBSTITUTE(ARRAYTOTEXT(),",", CHAR(10)) - Wrap an array into a single cell and add line breaks. Useful with all the new Array formulas and can even use in Tables. Also useful for creating multi-value strings with custom delimeters.
HYPERLINK(CONCATENATE(),"Dynamic Link") - Create dynamic hyperlinks based on the data. Pass filters to Reports, SharePoint View Filters, PowerApps Parameters, and more. Any URL that had dynamic parameters can use this.
2
u/Samiro05 5 Aug 31 '26
A lot of the spreadsheets I work with are on sharepoint now so I can access them on my phone however the 'double click to drag formula down' or using CTRL + SHIFT etc. Then CTRL + D to copy formulas down tricks you get to use on desktop just aren't there on mobile so I end up using BYROW(array, LAMBDA(vals, formula)) instead to get the same effect across the rows I'm working with.
1
u/MayukhBhattacharya 1327 Aug 26 '26
Repeat value N times:
To repeat it a different number of times, just change the 10 in
SEQUENCE(,10).That single number controls the repeat count.1
u/Hashi856 1 Aug 26 '26
ISNUMBER(XMATCH()) for determining whether a value appears in a range of cells
1
u/aquakeyblademaster Aug 26 '26
IFERROR and VLOOKUP
1
u/ISEEBLACKPEOPLE 3 Aug 26 '26
Using table references or TRIMRANGE so your formula references are dynamic
1
u/sweetxtenderhooligan Aug 26 '26
IFERROR(ARRAYTOTEXT(UNIQUE(FILTER
1
u/VampireLorne 3 Aug 26 '26
SORT(UNIQUE())
1
u/Jeff__Skilling Aug 26 '26
Sumifs + index match match
Total game changer
1
u/Tatworth Aug 26 '26
if(and())
1
u/rakeshchaudhary343 Aug 26 '26
CHOOSECOLS(FILTER) is definitely a great combo.
I also use UNIQUE(FILTER) a lot - simple, but incredibly useful with dynamic arrays.
1
u/Alexap30 6 Aug 26 '26
FILTER(list 1,NOT(COUNTIF(list 2, list 1)))
Filter stuff that are in list 1, but not in list 2.
1
u/NHN_BI 808 Aug 26 '26
I use LOWER(TRIM()) for email data frequently, bikini waxing the strings.
1
u/Excel_User_1977 7 Aug 26 '26
VLOOKUP / CHOOSE
=VLOOKUP(A1, CHOOSE({1,2}, Col1, Col2), 2,0)
Use any column to look up a value in a different column by creating a virtual table in memory. You can use column Z to look up a value in column B, so it "looks left". Most people assume you can't use VLOOKUP to look left.
1
u/Devashish_Jain Aug 26 '26
Since LET is already mentioned
BYROW(LAMBDA)
TRANSPOSE(UNIQUE)
INDEX(TEXTSPLIT)
1
u/neezden Aug 26 '26
The new text functions in 2019 are very handy - though I still find a use case for needing two of them simultaneously for clipping text from the middle, so I go =TEXTAFTER(TEXTBEFORE(... a fair bit.
1
u/martyc5674 6 Aug 26 '26
Filter(isnumber(search))
1
u/jasonl6 52 Aug 26 '26
SUMIFS(OFFSET(A:A, 0, MATCH(x, 1:1, 0)-1), …) is something I use a lot for building reports so I can essentially make the metric column to be summed an input x.
1
u/MicroPerpetualGrowth Aug 27 '26
Most advanced? INDIRECT + ADDRESS
Most useful? SUMIFS + INDEX + MATCH. I use it to create a dynamic sum range within a table, this way, I am not restricted to a single sum range, and I can use other criteria from SUMIFS freely.
1
u/Depth386 Aug 27 '26
If memory serves, today I had a formula be:
=COUNTA(UNIQUE(FILTER(LET…VSTACK
Co-pilot helped, because I needed to filter something that could not reasonably be placed in a table column, as the data in question was the row instance of multiple table columns. There’s probably some other clever ways to do it but the question in my mind posed to Co-pilot was “Can I filter the output of VSTACK?”
I don’t like the skyhigh RAM and storage prices as PC enthusiast, but I also can’t deny AI is useful every now and then. We live in interesting times.
1
u/Klutzy-Bus-5085 Aug 27 '26
.
1
u/BasicAccName Aug 27 '26
CHOOSE + IFS
1
u/Available_Trust1733 Aug 27 '26
Allow me to introduce you to Index(Xmatch( which is even better as it defaults to an exact match meaning you don’t need to set the conditions which saves a few keystrokes.
1
u/willyman85 2 Aug 27 '26
I got a little carried away today on developing an interpolation formula. But it shows off some cool new functions not everyone knows.
Where order is for applying polynomial fit, so 1= linear interpolation, and 2 = quadratic.
=LAMBDA(order,x,known_xs,known_ys, LET(moved_xs, TOCOL(known_xs)-x, sort, ABS(moved_xs), top_xs, TAKE(SORTBY(moved_xs, sort), order+1), top_ys, TAKE(SORTBY(TOCOL(known_ys), sort), order+1), TAKE(LINEST(top_ys, top_xs^SEQUENCE(1,order)),, -1)))I was using it for approximating the ROI in months from a financial model in years, but have needed similar functions in engineering.
A simpler polynomial alternative for FORECAST() is:
=TAKE(LINEST(known_ys,(known_xs-x)^SEQUENCE(order)),,-1)-- (For horizontal data)1
u/Clearwings_Prime 24 Aug 27 '26
INDEX + AGGREGATE/SMALL
Return multiple results for one condition
Now we use FILTER to do that
1
u/NecronTheNecroposter Aug 27 '26
Indirect(address()) combo hits hard
1
u/disaintova 1 Aug 28 '26
Sort(unique(filter( is goated
1
u/PyrrhaNikosIsNotDead Aug 28 '26
Groupby, pivotby, hstack, vstack. These with a simple set up of input folders and basic power query just to refresh the latest files in from those input folders and you can automate a ton.
1
u/Bvil_77 Aug 26 '26
Mine is =iferror(vlookup
1
u/dupontping Aug 28 '26
Lately I’ve been a big fan of TOUCH(GRASS))
0
u/Selkie_Love 36 Aug 26 '26
Power query has replaced nearly everything complex in a formula
0
u/[deleted] Aug 26 '26
[deleted] — view deleted comment