Is INDEX & MATCH formula Inefficient?

[Folshot]

I have created the following formula in column L of my spreadsheet:

Please Login or Register  to view this content.

Basically the formula does the following:
1) concatenate the 'Country', 'Parent Chassis S/N', 'Service Start Date' columns and adds CHASSIS to the string
2) find this value in column P (which is a helper column I created; the formula in this column concatenates the 'Country', 'Parent Chassis S/N', 'Service Start Date' and 'Device Type' columns)
3) If a match exists, return the associated value from column O into column L, otherwise return the minimum value between column I, J and K.

The goal of this formula is for every module associated to a chassis [i.e. modules (in column B) having the same parent chassis S/N (in column A)] to have their Effective End Date never 'higher' than the one from their associated chassis.

The formula works fine, but it does not seem to be very efficient, specially since the complete spreadsheet contains around 30,000 rows.

Is there a way to create my formula more efficiently?
Is there a way to get rid of those helper columns (i.e. O and P) I created?

Thanks a lot,
Ant

[Jonmo1]

I'd say that's about as efficient as it can get.

My only suggestion would be to not use entire column references like O:O and P:P
Instead, use finite row #s like O$1:O$30000


Also, this may just because you've simplified your book for our benefit...

But it seems the values in Column O that you're indexing are the result of the same MIN function you use in IFERROR if the INDEX is an error.
So why bother using the INDEX to begin with?
Why not just use the MIN value and forget about the index/match.

[JBeaucaire]

Yes, there are OTHER ways to accomplish what you've done without the helper column. But without your helper column you would be creating an array in EVERY cell of column L, an array if 30k options, so that would be extraordinarily less efficient than what you have already.

I would do exactly what you've done, then possibly hide the helper column. Maybe not hide it.


When using whole columns in an INDEX/MATCH, there is no loss of efficiency like there are with other functions. It's an awesomely robust function.

[Pete_UK]

Another thing to bear in mind is do you need those formulae to be active once they have calculated? If the data is essentially static, then once your have applied the formulae you could think about fixing the values so then you wouldn't need the helper columns - just re-apply the formulae for any new data added, then fix those values.

Hope this helps.

Pete

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Folshot]

Hi Jonmo1,

To reply to your comments:

But it seems the values in Column O that you're indexing are the result of the same MIN function you use in IFERROR if the INDEX is an error. So why bother using the INDEX to begin with? Why not just use the MIN value and forget about the index/match.

The formula retrieve the lowest date between columns I, J and K for the associated chassis and applies to its modules in column L, unless this chassis 'lowest' date is 'bigger than the module 'lowest' date (in this case the formula is simply using the module 'lowest' date). Not sure if this makes sense but I think it works pretty well.


Hi Jerry,

When using whole columns in an INDEX/MATCH, there is no loss of efficiency like there are with other functions.

Thanks for the tip, I was reluctant to use a finite range and named range as the end users won't be able to change those formulas, glad to know using the whole columns is not much of a problem.


Hi Pete,

I do need the formulas to be active as the values can change very often, also the end users will not be able / do not have the skills to do any changes.


To all, I think the efficiency issue might be related to another function we created. It works fine but it increases the size of the file by 2 (when compared to using static values). This is very strange... we are still investigating, might open a separate thread if we cannot come to any conclusions. Thanks all for your help, I will close this thread.