Index Match (Small?) with multiple criteria

[kazbear]

Hello.

I trying to get results from a report I run, and I a, attempting to match a couple criteria.

The report will have more than one row that will match the first criteria. Then, from those, one will match the second. Then I can get the value I'm looking for.

Im using Index/Match for some other formulas, but I think the part that complicating this one is the multiple matches (rows). I have used SMALL before, but I cant quite figure out how to put it all together. Any help is appreciated.

This is what I have so far:
=INDEX(Report!$A$2:$O$132,MATCH(1,(Display!$B4,Report!$A$2:$A$132)*(Display!E$3,Report!$K$2:$K$132),13))

I know I may need to enter as an Array, though would not mind avoiding that...

 

[stovvy]

Finally got it with =INDEX(Data!D:D,MATCH(C8,(Data!E:E)*("Blue"=Data!I:I),0))

 

[Fluff]

Try
=INDEX(Data!$D$2:$D$132,AGGREGATE(15,6,(ROW(Data!$D$2:$D$132)-ROW(Data!$D$2)+1)/(($C8=Data!$E$2:$E$132)*("Blue"=Data!$I$2:$I$132)),1))
I would advise against you whole column references in array formulae.

 

[stovvy]

Thank you, but for future reference why is it a bad idea just to use C:C for example when using arrays? Many, many thanks

 

[Fluff]

Because Xl has to look at every cell in each of those columns, that's 1,048,576 times 3 so in excess of 3 million cells every time it calculates, which can slow down your workbook massively.