Try the following...

M2:

=SUMPRODUCT(--(ClinPath!C2:C5=K2),--(ClinPath!D2:D5=K3),--(ClinPath!E2:E5=K4),--(ClinPath!I2:I5=""))

N2, copied down:

=IF(ROWS(N$2:N2)<=$M$2,INDEX(ClinPath!B$2:B$5,SMALL(IF((ClinPath!$C$2:$C$5=$K$2)*(ClinPath!$D$2:$D$5=$K$3)*(ClinPath!$E$2:$E$5=$K$4)*(ClinPath!$I$2:$I$5=""),ROW(ClinPath!$B$2:$B$5)-ROW(ClinPath!$B$2)+1),ROWS(N$2:N2))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Quote Originally Posted by malik641
sure

(In "ClinPath" worksheet)

B1:Study #....C1:Freezer....D1:Location....E1:Column..........I1:Disposed/Expired
B2:123...........C2:CP026.....D2:1st Shelf....E2:1st Column....I2:'Blank'
B3:456...........C3:CP026.....D3:1st Shelf....E3:1st Column....I3:'Blank'
B4:789...........C4:CP026.....D4:1st Shelf....E4:1st Column....I4:Disposed
B5:112...........C5:CP026.....D5:1st Shelf....E5:1st Column....I5:'Blank'

Here's what I need to do...
Display each study # based on the criteria:

1:Lookup freezer CP026
2:Lookup location
3:Lookup column (of freezer in column 'E')
4:Check if column 'I' is blank (If it has text, it will be ommitted)

Lets just say I put whatever formula this would be in 4 cells. This is what I should see:

(In 'Freezer Diagrams' worksheet)

A1:123
A2:456
A3:112
A4:'Blank'

That's it. It should be an INDEX formula based on 4 criteria, but when I make a formula up for it, I get this:

A1:123
A2:123
A3:123
A4:123

So it defaults to the VERY first Cell in the reference range, whether or not the criteria matches (if you want to see the formula it is posted above).

So I don't know what's the matter...