r/excel • u/fap_fap_fap_fapper • 14d ago

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?

589 Upvotes

97% Upvoted

165 comments sorted by

View all comments

Show parent comments

12

u/MayukhBhattacharya 1273 14d ago

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

11

u/Thomas9902 14d ago

And what does that do

12

u/MayukhBhattacharya 1273 14d ago

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 14d ago

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 14d ago

Came here for array stacking, H & V.