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]

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

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.

[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...

[Domenic]

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!

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...

[malik641]

That worked great! Thanks a lot. I have one more question though. What would I do if I wanted to continue the formula into the next column as 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!

That worked great! Thanks a lot. I have one more question though. What would I do if I wanted to continue the formula into the next column as 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

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