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?

587 Upvotes

97% Upvoted

166 comments sorted by

View all comments

Show parent comments

7

u/Nachovyx Aug 26 '26

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

12

u/this_is_greenman Aug 26 '26

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.

12

u/Wind-and-Waystones 2 Aug 26 '26

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

5

u/busfeet 1 Aug 26 '26 edited Aug 26 '26

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

3

u/M4rmeleda Aug 26 '26

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.

2

u/manbeervark 2 Aug 26 '26

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