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...

 

[James006]

Hello,

Is your Index Match combination supposed multiple results ...?

If it is the case ... then you will need an Array Formula and Small to extract individual elements ...

Hope this will help

 

[Fluff]

How about this array formula
=INDEX(Report!$M$2:$M$132,MATCH(1,($B4=Report!$A$2:$A$132)*(E$3=Report!$K$2:$K$132),0),1)
or this normal formula
=INDEX(Report!$M$2:$M$132,AGGREGATE(15,6,(ROW(Report!$M$2:$M$132)-ROW(Report!$M$2)+1)/(($B4=Report!$A$2:$A$132)*(E$3=Report!$K$2:$K$132)),1))

 

[kazbear]

How about this array formula
=INDEX(Report!$M$2:$M$132,MATCH(1,($B4=Report!$A$2:$A$132)*(E$3=Report!$K$2:$K$132),0),1)
or this normal formula
=INDEX(Report!$M$2:$M$132,AGGREGATE(15,6,(ROW(Report!$M$2:$M$132)-ROW(Report!$M$2)+1)/(($B4=Report!$A$2:$A$132)*(E$3=Report!$K$2:$K$132)),1))

Thank you for this. It works great. A couple of things I have never used before, so this is awesome!

 

[Fluff]

You're welcome & thanks for the feedback

 

[stovvy]

Hi,

I think I have a similar question as above, but cannot see where the references are. The data below is a sample of my data and rows A to I. In the next picture I have tried to look for the oldest case where it says Blue in Column I but, then look up the Number and the Surgery that corresponds to it.

I have got it to look up the longest date in the data below with Col C containing the date and its wait in Col D using {=SMALL(IF(Data!I:I="Blue",Data!E:E),1)}. However, I cannot get it to look up corresponding Number and Surgery to match.

Any help would be gratefully received

 

[Fluff]

Have you tried modifying either of the formulae in post#3 to suit your needs?

 

[stovvy]

I have tried but as I said I cannot see the corresponding values in the previous data. All I seem to get is a #N/A

 

[Fluff]

How did you modify the formula?

 

[stovvy]

Using =INDEX(Data!D:D,MATCH(SMALL(Data!E:E,1),Data!E:E,0)) I can retrieve the earliest Number, but I want to use a second criteria of if it is Blue in Col I.

I tried to modify your formula with =INDEX(Data!D:D,MATCH(C8,(Data!E:E)*("Blue"=Data!I:I),0),1) but seem to be getting nowhere