conditional look up formula issue...

[Kerrold]

I am currently using the following formula but need to add another condition to it and I keep coming up blank

{=IF($E4<>"",INDEX(DATA!$B$2:$B$5000,SMALL(IF(DATA!$Z$2:$Z$5000=$E4,ROW(DATA!$Z$2:$Z$5000)-ROW(DATA!$Z$2)+1),COUNTIF($E$4:$E4,$E4))),"")}

As well as the above i need the result to only be returned if the database entry is still active this would be in cells aa2:aa5000

Can this be done with this formula??

Many thanks in advance, Kerry

 

[Aladin Akyurek]

It would be in cell B4, thank you

B4, control+shift+enter, not just enter, and copy down:

=IF($E4="","",IFERROR(INDEX(DATA!$B$2:$B$5000,
  SMALL(IF(DATA!$AA$2:$AA$5000,IF(DATA!$Z$2:$Z$5000=$E4,
  ROW(DATA!$Z$2:$Z$5000)-ROW(DATA!$Z$2)+1)),COUNTIF($E$4:$E4,$E4))),""))

 

[Kerrold]

Thank you

 

[Aladin Akyurek]

Thank you

You are welcome.