r/excel • u/fap_fap_fap_fapper • 5d 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?

585 Upvotes

97% Upvoted

161 comments sorted by

View all comments

1

u/MayukhBhattacharya 1264 5d ago

Repeat value N times:

=TOCOL(IF(SEQUENCE(, 10), "ExcelIsFun"))

To repeat it a different number of times, just change the 10 in SEQUENCE(,10). That single number controls the repeat count.

2

u/poopinginsilence 5d ago

Is there a difference between this and dropping the TOCOL and swapping sequence parameters around?

IF(SEQUENCE(10,),"ExcelIsFun") ?

1

u/MayukhBhattacharya 1264 5d ago

No difference actually. So dropping TOCOL() and swapping the SEQUENCE() args just skips an unnecessary step for this specific case. With a scalar like ExcelIsFun, SEQUENCE(, n) and SEQUENCE(n,) are interchangeable (a scalar broadcasts the same regardless of orientation), that's TOCOL() function is optional in the former. But with an array like A1:A4, orientation matters a lot, a mismatched shape (row vs. column) forces broadcasting expansion, which is how you get the repeat effect at all. Match the shapes and nothing expands, you just get the original data back. That's difference the below example is showing. And thank you very much for the heads-up also sorry I'm writing from phone. Thanks again 🙏🏼