INDEX problem

  • Thread starter Thread starter malik641
  • Start date Start date
M

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.
 
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!
 
It's not clear to me what it is you're looking for. Can you provid
some sample data, along with 'expected results'?
 
sure

(In "ClinPath" worksheet)

B1:Stud
#....C1:Freezer....D1:Location....E1:Column..........I1:Disposed/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....I4:Disposed
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..
 
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!
 
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

????????????
 
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!
 
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!
 
Back
Top