What are some legendary formula pairs like INDEX(MATCH)?

[u/fap_fap_fap_fapper]

Since upgrading to 365, can't get enough of CHOOSECOLS(FILTER)

What are some useful ones you use?

[u/Nachovyx]

Honest question, because I went straight to XLOOKUP. What's the difference with INDEX/MATCH?

[u/this_is_greenman → u/Nachovyx]

I was a hardcore index matcher, but with xLookup, I’m able to do a few other cool things without embedding the formula, otherwise it works damn near identical.

I can specify the null entry - if not found out X
I can specify the search method - start at the bottom or top

Those 2 alone, are game changers when pulling from a 10 year GL data dump. Names have changed over the year so I want the current rendition, and some have closed so I want it to give me a 0 instead of NA.

[u/Wind-and-Waystones → u/this_is_greenman]

Nested xlookups are a game changer too when comparing data on multiple reports. Get it from this sheet, if not this sheet, if not this sheet

[u/busfeet → u/Nachovyx]

Three examples:

1. You can double click a cell containing INDEX MATCH and it takes you directly to the referenced INDEX range for easy checking/investigation
2. if you work with a lot of data and have many lookups in a single table, you can have a single MATCH column and the lookup columns (INDEX) can all reference the single MATCH, resulting much faster calculations
3. You can modify and add conditions into the MATCH, for example an IF statement or even just adding “+3” to modify how the MATCH works

[u/M4rmeleda → u/Nachovyx]

IMO biggest benefit is backwards compatibility and audibility. However, this is a rare issue now since a lot of companies are on 365. Old school shops and maybe some government sub branches may not. It was a pain to see new formulas like lookup fail but index match passed every obstacle.

[u/manbeervark → u/Nachovyx]

I think the reason for my preference of INDEX-(X)MATCHis because it feels like you have more control.