INDEX problem

[malik641]

I want to index reference numbers from column B based on Columns C,D,E,
and I

Column B has the reference number
Column C is the Freezer Name
Column D is the location (i.e. Shelf number)
Column E is the Column in the freezer
Column I is any Transfer/Used/(any text)

I'm checking these columns from a worksheet called ClinPath. The
worksheet that calls those columns is called 'Freezer Diagrams'.

'Freezer Diagrams' worksheet is just a table that is set up to look
like the freezers that the reference numbers are stored in. I just want
to show where each reference number is stored for quick accessing.

here's what I have:

{=INDEX(ClinPath!$B$2:$B$5000,(ClinPath!$I$2:$I$5000=$A$1)*(ClinPath!$C$2:$C$5000=K$2)*(ClinPath!$D$2:$D$5000=K$3)*(ClinPath!$E$2:$E$5000=K$4))}

$A$1 is just a blank cell
K$2 is the Freezer Name
K$3 is the Shelf Number
K$4 is the Column Number (in the freezer)

The formula is not referencing anything except the VERY first cell in
the reference that does not match the criteria at all.

 

[Domenic]

See if this is what you're looking for...

=INDEX(ClinPath!$B$2:$B$5000,MATCH(1,(ClinPath!$C$2:$C$5000=K$2)*(ClinPath!$D$2:$D$5000=K$3)*(ClinPath!$E$2:$E$5000=K$4),0))

Hope this helps!

 

[Domenic]

It's not clear to me what it is you're looking for. Can you provid
some sample data, along with 'expected results'?

 

[malik641]

sure

(In "ClinPath" worksheet)

B1:Stud
#....C1:Freezer....D1:Location....E1:Column..........I1isposed/Expired
B2:123...........C2:CP026.....D2:1st Shelf....E2:1s
Column....I2:'Blank'
B3:456...........C3:CP026.....D3:1st Shelf....E3:1s
Column....I3:'Blank'
B4:789...........C4:CP026.....D4:1st Shelf....E4:1s
Column....I4isposed
B5:112...........C5:CP026.....D5:1st Shelf....E5:1s
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 i
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 whe
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, whethe
or not the criteria matches (if you want to see the formula it i
posted above).

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

 

[Domenic]

Try the following...

M2:

=SUMPRODUCT(--(ClinPath!C2:C5=K2),--(ClinPath!D25=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!

 

[malik641]

That worked great! Thanks a lot. I have one more question though. Wha
would I do if I wanted to continue the formula into the next column a
if it was below the original cells with the formula.

Example for the formula you gave me:

K5:Formula.....L5:Formula continued from K9
K6:Formula.....L6:Formula after L5
K6:Formula.....L7:Formula after L6
K7:Formula.....L8:Formula after L7
K8:Formula.....L9:Formula after L8
K9:Formula.....L10:Formula after L9

????????????

 

[malik641]

Nevermind I got it.

Here is K14's formula

{=IF(ROWS(K$5:K14)<=$J$2,INDEX(ClinPath!$B$2:$B$5000,SMALL(IF((ClinPath!$C$2:$C$5000=$K$2)*(ClinPath!$D$2:$D$5000=$K$3)*(ClinPath!$E$2:$E$5000=$K$4)*(ClinPath!$I$2:$I$5000=""),ROW(ClinPath!$B$2:$B$5000)-ROW(ClinPath!$B$2)+1),ROWS(K$5:K14))),"")}

and then here's L5's formula

{=IF((ROWS($K$5:$K$14)+ROWS(L$5:L5))<=$J$2,INDEX(ClinPath!$B$2:$B$5000,SMALL(IF((ClinPath!$C$2:$C$5000=$K$2)*(ClinPath!$D$2:$D$5000=$K$3)*(ClinPath!$E$2:$E$5000=$K$4)*(ClinPath!$I$2:$I$5000=""),ROW(ClinPath!$B$2:$B$5000)-ROW(ClinPath!$B$2)+1),(ROWS($K$5:$K$14)+ROWS(L$5:L5)))),"")}

Thanks a lot Domenic!

 

[Domenic]

Try...

K5, copied to K10:

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

L5, copied to L10:

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

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!