Index and Match with Multiple Results

Hi,

I'm self taught in excel so excuse my ignorance on some things. I've been working hard trying to get an index and match to work with 3 search criteria and i've finally been succesful using the below formula.

=INDEX(Inputs!R1:R1000,MATCH(D2&I2&"Gas to be Tested:",INDEX(Inputs!F1:F1000&Inputs!D1:D1000&Inputs!P1:P1000,),0))

The next step is, I have multiple correct results in my dataset for this search criteria. I want to use this formula to give the next correct search result on the row below. Anybody any advice they can give me?

Thanks for the help!

Dan

PS - This formula just wouldn't work in my initial work

{=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))}

You cannot do that with match, try aggregate instead

Formula:

Please Login or Register  to view this content.

That's Great!

Thanks so much I just could not figure out how to write that at all, works a treat!

Just had to fix a couple parts

=INDEX(Inputs!$R$1:$R$1000,AGGREGATE(15,6,(ROW(Inputs!$R$1:$R$1000)-ROW(Inputs!$R$1)+1)/($D$2=Inputs!$F$1:$F$1000)/($I$2=Inputs!$D$1:$D$1000)/("Equipment Description"=Inputs!$P$1:$P$1000),ROWS(A$2:A2)))

Thanks so much!

Glad to help & thanks for the feedback.