Index and lookup

[jimblimm]

My setup


(day)
a1:a10 b1:b10 c1:c10 d1:d10 e1:e10
1 0 1 2 3
2 1 0 2 0
3 2 3 5 4
4 0 0 2 3
5 0 1 2 3
6 5 8 9 6
7 7 5 3 4
8 0 1 2 3
9 0 0 2 0
10 1 2 3 4

i need a formula to lookup the last time the value "0" was seen out of all 4 ranges

answer in f1=9(in reference to a1:a10(days)

 

[T. Valko]

My setup


(day)
a1:a10 b1:b10 c1:c10 d1:d10 e1:e10
1 0 1 2 3
2 1 0 2 0
3 2 3 5 4
4 0 0 2 3
5 0 1 2 3
6 5 8 9 6
7 7 5 3 4
8 0 1 2 3
9 0 0 2 0
10 1 2 3 4

i need a formula to lookup the last time the value "0" was seen out of all 4 ranges

answer in f1=9(in reference to a1:a10(days)

Row 9 doesn't contain 4 zeros?

Maybe this...

Book1
	A	B	C	D	E	F
1	1	0	1	2	3	9
2	2	1	0	2	0	_
3	3	2	3	5	4	_
4	4	0	0	2	3	_
5	5	0	1	2	3	_
6	6	5	8	9	6	_
7	7	7	5	3	4	_
8	8	0	1	2	3	_
9	9	0	0	0	0	_
10	10	1	2	3	4	_
Sheet1

This formula entered in F1:

=LOOKUP(2,1/(B1:B10&C1:C10&D1:D10&E1:E10="0000"),A1:A10)

 

[jimblimm]

sorry for misconception

if any of the rows contain "0"
all of them dont have to be "0"

i just want to know when was the last day a "0" was seen in either range b1:e1

 

[T. Valko]

sorry for misconception

if any of the rows contain "0"
all of them dont have to be "0"

i just want to know when was the last day a "0" was seen in either range b1:e1

Ok, try this...

=LOOKUP(2,1/(B1:E10=0),A1:A10)

 

[jimblimm]

worked perfect, i swear u r a genius.

now using that same fashion, is there a way to count all the "0" in the ranges

 

[T. Valko]

worked perfect, i swear u r a genius.

now using that same fashion, is there a way to count all the "0" in the ranges

Like this...

=COUNTIF(B1:E10,0)

 

[jimblimm]

Ok, try this...

=LOOKUP(2,1/(B1:E10=0),A1:A10)

How can i add s second criteria, g1:g10="s"
I also will need it add to the countif you mentioned

 

[T. Valko]

How can i add s second criteria, g1:g10="s"

Like this:

=LOOKUP(2,1/((B1:E10=0)*(G1:G10="s")),A1:A10)

I also will need it add to the countif you mentioned

For this, I'll need to see sample data and tell me what result you expect.

 

[jimblimm]

Like this:

=LOOKUP(2,1/((B1:E10=0)*(G1:G10="s")),A1:A10)


For this, I'll need to see sample data and tell me what result you expect.

i tried that and for some reason its not calculating right. i had to do it like this

=MAX(LOOKUP(2,1/((LOOKUP(2,1/((B1:b10=0)*(G1:G10="s")),A1:A10),LOOKUP(2,1/((c1:c10=0)*(G1:G10="s")),A1:A10),LOOKUP(2,1/((d1:d10=0)*(G1:G10="s")),A1:A10),LOOKUP(2,1/((e1:E10=0)*(G1:G10="s")),A1:A10)... please tell me there is a shorter way

 

[T. Valko]

i tried that and for some reason its not calculating right. i had to do it like this

=MAX(LOOKUP(2,1/((LOOKUP(2,1/((B1:b10=0)*(G1:G10="s")),A1:A10),LOOKUP(2,1/((c1:c10=0)*(G1:G10="s")),A1:A10),LOOKUP(2,1/((d1:d10=0)*(G1:G10="s")),A1:A10),LOOKUP(2,1/((e1:E10=0)*(G1:G10="s")),A1:A10)... please tell me there is a shorter way

Try this array formula**:

=INDEX(A1:A10,MAX(IF(B1:E10=0,IF(G1:G10="s",ROW(A1:A10))))-ROW(A1)+1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.