0

I'm running the same MATCH function in my spreadsheet numerous (say 100) times.

Is it better/worse/no difference to code the function 100 times, into each cell where it's used, rather than do it once in some other cell and then refer to the value of that cell 100 times?

I'm interested mainly in the effect on code efficiency (but I think it also impacts code readability).

From what I understand, it might make no difference given the way Excel does or does not recalculate everything when the data changes (MATCH is non-volatile, I believe?).

Full context:

Columns J onward contains monthly data (J has July 2015, K has Aug 2015, etc).

Elsewhere in the spreadsheet, I want to produce various summary information (e.g. total widgets built last quarter), but I want the user to be able to specify the month they're interested in (e.g. if the user enters June 2015 into a cell, the last quarter is Jan-Mar, if they enter July 2015 it's Apr to Jun).

So in my summary info, I need to:

  1. Look up the column corresponding to the month the user has entered.
  2. Do various calculations based on the information in the previous few columns.
Grant Miller
  • 27,532
  • 16
  • 147
  • 165
Steven R
  • 1
  • 1
    you need a database. Also show what you've tried to achieve this – M O'Connell Nov 10 '16 at 03:40
  • A DB isn't an option for various reasons, not least of which are that a number of people will be using this and they won't be trained up in SQL and some kind of user-friendly frontend isn't in the cards. – Steven R Nov 10 '16 at 04:05
  • To clarify: I have achieved this. An example of the formulae I'm using is `=INDEX($1:$46,ROW(),MATCH($F$1,$5:$5))` where `$F$1` is the cell where the user enters the month they're interested in. My question is really around the performance issues of having that `MATCH($F$1,$5:$5)` being placed in 100 cells, rather than just putting it in cell `$Z$50` and replacing the match with a reference to `$Z$50`. – Steven R Nov 10 '16 at 04:08
  • What about Pivot tables, Simply add filters and let users interact that way? – M O'Connell Nov 10 '16 at 04:09
  • I'm not overly familiar with pivot tables, but I'll look into this. Thanks. But any views on the performance issue of repeating the same MATCH hundreds of times vs reference to a cell that contains the MATCH result? – Steven R Nov 10 '16 at 04:11
  • 1
    Look at [Pivots with](https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576) `ReportFilters`. I cant really comment on performance. When Excel begins to act like a DB I force users to move to a DB. If your tech savvy you could try [KNIME](https://www.knime.org/), it'll deal with data better than excel and you can at least create data flows. – M O'Connell Nov 10 '16 at 04:15

0 Answers0