INDEX/MATCH with 2 Criteria ??

[Aitch]

Is there a way to use 2 conditions with an INDEX/MATCH ?

I have something like this, which finds the non-blank cell in column B

INDEX(B:B , MATCH("??*" , B:B , 0)

But I need to also make sure the non-blank cell has value 3 in column D - so...

INDEX(B:B , MATCH("??*" , B:B , 0) + MATCH("3" , D:D , 0)

Can this be achieved without using an Array?

Thanks for any help!

 

[AhoyNC]

Maybe this link will be of some help. If not post a small sample and the result you expect.

 

[Saurabhj]

Hi, the below code will solve but I am using array. So, need to press ctrl+shift+enter after writing formula:

=INDEX($B$1:$B$6,MATCH(1,(NOT($B$1:$B$6="??*"))*($D$1:$D$6=3),0))

 

[AhoyNC]

Try:

INDEX($B$1:$B$6,AGGREGATE(15,6,(ROW($B$1:$B$6)-ROW($B$1)+1)/(($B$1:$B$6<>"??*")*($D$1:$D$6=3)),1))