Index Function

[Guest]

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:AS14>B18),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.

 

[Biff]

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

 

[Guest]

Thanks Biff. Much appreciated. Point taken.

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

 

[Biff]

You're welcome!

Biff

Thanks Biff. Much appreciated. Point taken.