LinkBack URL
About LinkBacks
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.
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.
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.....
Audere est facere
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
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
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.
Try..in D11
or
Please Login or Register to view this content.
copy down and acrossPlease Login or Register to view this content.
Life's a spreadsheet, Excel!
Say thanks, Click *
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks