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

 

[Kirtiraj251093]

Hi kerry,

can you explain a bit in brief so that i can understand,

1. what criteria you need to add
2. it would be more easy for me if you can provide me the excel file or else format in which the data is sorted

if you dont recieve reply soon then

mail me at poojaryworkbook@gmail.com

 

[Aladin Akyurek]

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

Yes. What is the criterion/condition aa2:aa5000 must meet?

 

[Kerrold]

Hi, sorry for the delay i've been off work....
The criteria is basically if the case is closed or active - i need to return the result only for an active case

Thank you

 

[Aladin Akyurek]

Hi, sorry for the delay i've been off work....
The criteria is basically if the case is closed or active - i need to return the result only for an active case

Thank you

That's great. What must the forumula test AA2:AA5000?

 

[Kerrold]

Hope this makes sense....

If the case is active as shown in column 'aa' i want it to return the following but for the life of me can't seem to work out where to add this condition...

{=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))),"")}

I have a sample spreadsheet i could attach but can't find how to attach it!

Kerry

 

[Aladin Akyurek]

Hope this makes sense....

If the case is active as shown in column 'aa' i want it to return the following but for the life of me can't seem to work out where to add this condition...

{=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))),"")}

I have a sample spreadsheet i could attach but can't find how to attach it!

Kerry

I think I'm not making myself clear. How do we test in AA whether something is active?

 

[Kerrold]

I think I'm not making myself clear. How do we test in AA whether something is active?

Sorry the column just has the words active or closed in it so need to find results for entries that "active" only

 

[Aladin Akyurek]

Sorry the column just has the words active or closed in it so need to find results for entries that "active" only

Right. Thank you. One more: In which cell do we need to enter the formula?

 

[Kerrold]

It would be in cell B4, thank you