Index Match with Multiple Criteria (incl. wildcard & greater than a value)

[chrisou]

Hello Excel experts
I am trying to match 3 criteria (one with a wildcard) using INDEX and MATCH in the attached excel file;

Exact DATE + Abbreviated FORMAT + No of trays >0

I have tried 2 formulas that I would normally use but neither works exactly with the 3 definezd criteria
I would very much appreciate your advice on where I am going wrong

Many thanks
Chris

[AliGW]

What time are you expecting to return?

How about this?

=INDEX(B2:B10,MATCH(1,(A2:A10=H3)*(D2:D10>0)*(C2:C10=H4),1))

Change commas to semi-colons for your French locale.

[JohnTopley]

Try

=INDEX($B$2:$B$10,AGGREGATE(15,6,ROW($A$1:$A$10)/($A$2:$A$10=$H$3)/($D$2:$D$10>0)/(LEFT($C$2:$C$10,LEN($H$4))=$H$4),1))

[samba_ravi]

try below
=LOOKUP(2,B2:B10/((A2:A10=H3)*SEARCH(H4,C2:C10)*(D2:D10>0)))

[chrisou]

Thanks to all of you for your replies. John, your formula works perfectly in all the tested conditions. I have less knowledge about the aggregate function and I would never have imagined using it. Really happy to have been in contact with you guys. Great support and great site

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[chrisou]

John, if I was interested in the last value which satisified all 3 criteria and not the first value, how would the formula change?

[AliGW]

No further responses here, please. The OP has started a follow-up thread here: https://www.excelforum.com/excel-for...-wildcard.html