Index Function
 

I am using the following Index formula to look at the values in certain cells
(every 7th column in a given row) and see if they exceed a certain value and
for the
first one that does, I would like the result to be what appears in the same
column but in a row above.

=INDEX(X2:AS2,MATCH(1,(MOD(COLUMN(X14:AS14),7)=1)* (X14:AS14B18),0))

(entered as an array)

For example, I have the values 5, 7, 10, 15 in cells X14, AD14, AJ14 and so
on
the formula would look for the first instance of a value above 4 (in this
case cell X14) and the result would be the contents of cell X2. Problem is
that the formula appears to be skipping over the first couple of instances
and giving the result for AJ14 (i.e., the contents of cell AJ2). Do you know
where my mistake is? Thank you in advance for your assistance.

Index Function
 

That's why it's a good idea to tell us where your data really is located
instead of making up some "crap" ! <BG

Change this:

MOD(COLUMN(X14:AS14),7)=1

To:

MOD(COLUMN(X14:AS14),6)=0

Biff

"SJT" wrote in message
...
I am using the following Index formula to look at the values in certain
cells
(every 7th column in a given row) and see if they exceed a certain value
and
for the
first one that does, I would like the result to be what appears in the
same
column but in a row above.

=INDEX(X2:AS2,MATCH(1,(MOD(COLUMN(X14:AS14),7)=1)* (X14:AS14B18),0))

(entered as an array)

For example, I have the values 5, 7, 10, 15 in cells X14, AD14, AJ14 and
so
on
the formula would look for the first instance of a value above 4 (in this
case cell X14) and the result would be the contents of cell X2. Problem
is
that the formula appears to be skipping over the first couple of instances
and giving the result for AJ14 (i.e., the contents of cell AJ2). Do you
know
where my mistake is? Thank you in advance for your assistance.

Index Function
 

Thanks Biff. Much appreciated. Point taken.

"Biff" wrote:

That's why it's a good idea to tell us where your data really is located
instead of making up some "crap" ! <BG

Change this:

MOD(COLUMN(X14:AS14),7)=1

To:

MOD(COLUMN(X14:AS14),6)=0

Biff

"SJT" wrote in message
...
I am using the following Index formula to look at the values in certain
cells
(every 7th column in a given row) and see if they exceed a certain value
and
for the
first one that does, I would like the result to be what appears in the
same
column but in a row above.

=INDEX(X2:AS2,MATCH(1,(MOD(COLUMN(X14:AS14),7)=1)* (X14:AS14B18),0))

(entered as an array)

For example, I have the values 5, 7, 10, 15 in cells X14, AD14, AJ14 and
so
on
the formula would look for the first instance of a value above 4 (in this
case cell X14) and the result would be the contents of cell X2. Problem
is
that the formula appears to be skipping over the first couple of instances
and giving the result for AJ14 (i.e., the contents of cell AJ2). Do you
know
where my mistake is? Thank you in advance for your assistance.

Index Function
 

You're welcome!

Biff

"SJT" wrote in message
...
Thanks Biff. Much appreciated. Point taken.

"Biff" wrote:

That's why it's a good idea to tell us where your data really is located
instead of making up some "crap" ! <BG

Change this:

MOD(COLUMN(X14:AS14),7)=1

To:

MOD(COLUMN(X14:AS14),6)=0

Biff

"SJT" wrote in message
...
I am using the following Index formula to look at the values in certain
cells
(every 7th column in a given row) and see if they exceed a certain
value
and
for the
first one that does, I would like the result to be what appears in the
same
column but in a row above.

=INDEX(X2:AS2,MATCH(1,(MOD(COLUMN(X14:AS14),7)=1)* (X14:AS14B18),0))

(entered as an array)

For example, I have the values 5, 7, 10, 15 in cells X14, AD14, AJ14
and
so
on
the formula would look for the first instance of a value above 4 (in
this
case cell X14) and the result would be the contents of cell X2.
Problem
is
that the formula appears to be skipping over the first couple of
instances
and giving the result for AJ14 (i.e., the contents of cell AJ2). Do
you
know
where my mistake is? Thank you in advance for your assistance.