Lookup Multiple Criteria

[Kenshin]

I have problem with lookup using multiple criteria and multiple results and need resolved with formula


thank you

 

[rahulshewale1]

hi @Kenshin

See if is it ok ?

Copy across:

=IFERROR(@INDEX(C$5:C$31,AGGREGATE(15,6,(ROW($C$5:$C$31)-ROW($C5)+1)/((($C$5:$C$31>=$H$3)*($C$5:$C$31<=$I$3))*($B$5:$B$31=$G$3)),$F6)),"")

 

[Kenshin]

Thanks Rahul but when i changed the results to column B (Category), why I get incorrect results

 

[GraH - Guido]

Maybe this works =IF($F6>COUNTIFS($B$5:$B$31,$G$3,$C$5:$C$31,">="&$H$3,$C$5:$C$31,"<="&$I$3),"",INDEX(C$5:C$31,AGGREGATE(15,6,($A$5:$A$31)/(($B$5:$B$31=$G$3)*($C$5:$C$31>=$H$3)*($C$5:$C$31<=$I$3)),$F6)))

 

[Kenshin]

Like always your formula amaze me Guido, thanks

 

[GraH - Guido]

You're welcome.