Index-Match/Small based on multiple criteria

[WilliamWhite70]

So I've gotten the basic search to work:
{=INDEX('Combined Data'!$A$2:$C$1000000,SMALL(IF('Combined Data'!$B$2:$B$1000000=$B5,ROW('Combined Data'!$B$2:$B$1000000)),ROW(1:1))-1,3)}

Right now, it's looking just at the $B column of the array, and as it's a huge array with up to 30 instances of the data in that column, that's a lot of calculating every time I enter new data to search for.

What I'm wondering is: Can I add in another criteria for consideration?
The search data would be located in $B$3, and the location to compare B3 against would be 'Combined Data'!$A$2:$A$1000000

Thank you in advance for any help!

 

[FDibbins]

Hi, welcome to the forum

it's a huge array with up to 30 instances of the data in that column, that's a lot of calculating every time I enter new data to search for.

What I'm wondering is: Can I add in another criteria for consideration?

It's already big and you want to add more?

Anyway, yes you can, try this...
=INDEX('Combined Data'!$A:$C,SMALL(IF(('Combined Data'!$B$2:$B$1000000=$B5)*('Combined Data'!$AB$2:$A$1000000=$B3),ROW('Combined Data'!$B$2:$B$1000000)),ROWs($A$1:A1))),3)
(hope I got all the () in the right place)

edit: If you really do have 1 000 000 rows of data, that is going to be really slow, maybe consider using a helper column, instead of an array formula

If you don't have that many rows, reduce the range to more like what you actually need