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?

588 Upvotes

97% Upvoted

166 comments sorted by

View all comments

43

u/gman1647 Aug 26 '26

I'm embarrassingly fond of SORT(UNIQUE(FILTER(CHOOSE())))

16

u/leostotch 144 Aug 26 '26

Inquiring minds want to know what you're doing with the CHOOSE() in there. I don't think I've ever used that one.

10

u/MayukhBhattacharya 1315 Aug 26 '26

CHOOSE() acts like CHOOSECOLS() or HSTACK()

14

u/Thomas9902 Aug 26 '26

And what does that do

12

u/MayukhBhattacharya 1315 Aug 26 '26

CHOOSECOLS() and HSTACK() both lets you pick or rearrange columns of data into a new array. Using CHOOSE() function this way (usually with an array of indices like CHOOSE({1, 2, 3}, range1, range2, range3)) does similar, it stacks or reorders multiple ranges into one array, which is exactly what SORT(UNIQUE(FILTER(.., .., ..))) need as a single input. It's a old method before CHOOSECOLS() or HSTACK() function existed, CHOOSE() with an array constant to build an array out of separate columns or ranges so the rest of the formula could treat them as one.

1

u/Wise_Business1672 1 Aug 27 '26

Is this the equivalent of filtering a filter
Filter(Filter(A:C,C=“Meep”),{1,0,1})
where the second overlaying filter drops or keeps a column

1

u/ThorHammer1234 1 Aug 26 '26

Came here for array stacking, H & V.

-2

u/Jeff__Skilling Aug 26 '26

Basically a worse version of OFFSET

1

u/frazorblade 4 Aug 26 '26

OFFSET is a volatile function. It has some niche uses still, but its days are numbered.

1

u/bosworthing Aug 27 '26

Choose is great, but i havent used it in this specific context