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$50 00=$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
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=384870

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)*(ClinPa th!$E$2:$E$5000=K$4),0))

Hope this helps!

malik641 Wrote:
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$50 00=$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
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=384870

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

malik641 Wrote:
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$50 00=$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
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=384870

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
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=384870

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,SMAL L(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 Wrote:
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
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=384870

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
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=384870

Nevermind I got it.

Here is K14's formula

{=IF(ROWS(K$5:K14)<=$J$2,INDEX(ClinPath!$B$2:$B$50 00,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$500 0)-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(C linPath!$B$2:$B$5000,SMALL(IF((ClinPath!$C$2:$C$50 00=$K$2)*(ClinPath!$D$2:$D$5000=$K$3)*(ClinPath!$E $2:$E$5000=$K$4)*(ClinPath!$I$2:$I$5000=""),ROW(Cl inPath!$B$2:$B$5000)-ROW(ClinPath!$B$2)+1),(ROWS($K$5:$K$14)+ROWS(L$5:L 5)))),"")}

Thanks a lot Domenic!


--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=384870

Try...

K5, copied to K10:

=IF(ROW()-ROW(K$5)+1<=$M$2,INDEX(ClinPath!B$2:B$5000,SMALL(I F((ClinPath!$C$2:$C$5000=$K$2)*(ClinPath!$D$2:$D$5 000=$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(I F((ClinPath!$C$2:$C$5000=$K$2)*(ClinPath!$D$2:$D$5 000=$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!

malik641 Wrote:
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

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


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=384870