Offset/Match vs. Index/Match

[amartino44]

Hello. My boss uses a lot of offset/match formulas in her spreadsheets. From what I've been reading, index/match seems to be more common. Any reason why it is more common/preferable to offset/match? Thanks.

[daddylonglegs]

For finding a value in one column/row and returning a value from the same row/column INDEX/MATCH is probably preferable. INDEX is (in most forms) not "volatile", OFFSET is. This means that all your boss's OFFSET formulas will be recalculated every time any value in the sheet is changed, even if that value doesn't relate in any way to the formulas in question - many such formulas can affect the performance significantly.

Having said that, if there are a handful of formulas over small ranges then it probably doesn't make much difference.....

[Ron Coderre]

The OFFSET function is volatile. That means it recalculates whenever ANY cell in the workbook calculates. Beyond a certain threshhold, those needless recalcs can degrade the performance of the containing workbook. The INDEX function is not volatile.

[Palmetto]

FYI

Volatile Excel Functions @ Decision Models

[amartino44]

Hmmm, maybe that is why the spreadsheets we have are so unwieldy....the have TONS of offset/match formulas. I'm going to attach an example of what I have and if you guys could tell me if I could replace with index/match, I would appreciate it. Thanks. The spreadsheet has a Final tab and a Volume tab. We use offset/match to populate the final tab. Column R has unique identifiers and and column A has the tab name.

[Ace_XL]

Try..in D11

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

copy down and across