4

I have a 12x18 Excel range which draws data from a 823x20 sheet (Results!$A:$T) according to the 12x18 range's page, row and column headers (criterion1, criterion2 and criterion3, respectively)

={INDEX(Results!$A:$T,
        MATCH(1, (criterion1 = Results!$A:$A) * (criterion2 = Results!$B:$B), 0),
        MATCH(criterion3, Results!$A$1:$T$1, 0))}

As you can see, it's not that much data, but still, when I change the page header, my computer (2 GHz Intel Xeon with 24 GB of RAM) takes about a minute to update the 216 (12x18) index searches and I'm not even half way done with creating searches.

Earlier on my project, these searches only had one criterion, so I used VLOOKUP() and the results came very quickly. However, now I need to find values according to three criteria, and the function above is the best way I've managed to accomplish this. However, it seems to be the responsible for the long computation times I'm getting. So my question is: how can I optimize a VLOOKUP() of multiple criteria? Should I tweak INDEX(1, MATCH()*MATCH(), MATCH()) or is there a faster way to do this?

Here's a sample of the 12x18 index searches (branch name is the only variable that the end-user will be able to change):

Index

And of the 823x20 sheet it searches (column A has no merges, actually):

enter image description here

Ram
  • 3,092
  • 10
  • 40
  • 56
Waldir Leoncio
  • 10,853
  • 19
  • 77
  • 107
  • first option is to not use entire column ranges; second you could create one key column concatenating A & B so you can do a simple MATCH without needing an array formula – JosieP Aug 14 '13 at 13:59
  • 1
    @pnuts, according to the images, that 3 should be a 0. Actually, the second line of the first image should match the second line of the second image. That doesn't happend because the branch names are inconsistent between the images, please pay no heed to that. – Waldir Leoncio Aug 14 '13 at 14:07
  • @JosieP, thanks for the tip. I was trying to avoid concatenating cells and shrinking ranges, but if I see no other way, I might give this a try. – Waldir Leoncio Aug 14 '13 at 14:09
  • you might also try `={INDEX(Results!$A:$T, MATCH(1, IF(criterion1 = Results!$A:$A,IF(criterion2 = Results!$B:$B,1, 0),0), MATCH(criterion3, Results!$A$1:$T$1, 0))}` – JosieP Aug 14 '13 at 15:08

2 Answers2

1

For a summary and options for Lookup with multiple criteria you may check this.

I use a lot Method 2 here (it is non-array formula) and method here.

I guess you should try them to check their speed.

Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
0

First of all, can't this be accomplished simply by filtering on column A in sheet 'Results'? I think that is the most straightforward solution.

However, I have created an example workbook based on the criteria you described. It can be found here

First I created a list of the unique branches by using Advanced Filter for unique values on sheet 'Results' column A and put them on sheet 'Lists' where I created a named range listUnqBranches. I used that named range to create a data validation drop-down list on sheet 'Sheet1' cell A1 so that users can select which branch they'd like to see. I named that cell Branch.

Next I created two named ranges. rngDate is defined with this dynamic named range formula:

=INDEX(Results!$B:$B,MATCH(Branch,Results!$A:$A,0)):INDEX(Results!$B:$B,MATCH(Branch,Results!$A:$A,0)+COUNTIF(Results!$A:$A,Branch)-1)

rngLookup is defined with this dynamic named range formula:

=INDEX(Results!$C:$C,MATCH(Branch,Results!$A:$A,0)):INDEX(Results!$T:$T,MATCH(Branch,Results!$A:$A,0)+COUNTIF(Results!$A:$A,Branch)-1)

Lastly, in sheet 'Sheet1' cell B2 and copied over and down is this formula:

=IF(Branch="","",INDEX(rngLookup,MATCH($A2,rngDate,0),MATCH(B$1,Results!$C$1:$T$1,0)))

Note that the formula solution with named ranges is dependent on the data in sheet 'Results' being sorted by Branch. Does that work for you?

tigeravatar
  • 26,199
  • 5
  • 30
  • 38