Vlookup optimization with multiple criteria (index + match)

Question

I have an Excel 12x18 range that extracts data from an 823x20 sheet ( Results!$A:$T ) according to the 12x18 range headers, 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, this is not much data, but still, when I change the page title, my computer (2 GHz Intel Xeon with 24 GB of RAM) takes about a minute to update the 216 (12x18) index search and I didn’t even half way to create search queries.

Previously, in my project, these searches had only one criterion, so I used VLOOKUP() , and the results came very quickly. However, now I need to find the values ​​according to three criteria, and the function above is the best way I managed to do this. However, he seems to be responsible for the lengthy calculations I receive. So my question is: how can I optimize VLOOKUP() several criteria? Should I customize INDEX(1, MATCH()*MATCH(), MATCH()) or is there a faster way to do this?

Here's an example of a 12x18 index search (the name of the branch is the only variable that the end user can change):

And from the 823x20 sheet that he is looking for (column A doesn't really have merges):

Answer 1

For a summary and search options with several criteria, you can check.

I use a lot of Method 2 here (this is a formula without an array) and the method here .

I think you should try them to check the speed.

Answer 2

First of all, could this not be done simply by filtering on column A in the Results sheet? I think this is the easiest solution.

However, I created a sample book based on the criteria you described. Here you can find

First, I created a list of unique branches using the advanced filter for unique values ​​in the Results column of the Results sheet, and placed them in the Lists sheet, where I created the named range listUnqBranches. I used this named range to create a data validation drop-down list on sheet "Sheet1" A1 so that users can choose which branch they would like to see. I called this cell cell.

Then I created two named ranges. rngDate is defined using this dynamic range name 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 using this dynamic range name 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) 

Finally, in sheet “Sheet1”, cell B2 is copied above and below this formula:

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

Note that the solution to the formula with named ranges depends on the data in the Results sheet, sorted by branch. Does this work for you?