Match Function
 

I am attempting to create a formula that I believe needs to utilize the Match
function. I would like to look at the values in certain cells (every 4th
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.

For example, If I had the values 5, 7, 10, 15 in cells A4, E4, I4, and M4
the formula would look for the first instance of a value above 11 (in this
case cell M4) and the result would be the conents of cell M1. How would I
accomplish this. Thank you in advance for your assistance.

Match Function
 

Try this:

A10 = 11

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A1:M1,MATCH(1,(MOD(COLUMN(A4:M4),4)=1)*(A4: M4A10),0))

Biff

"SJT" wrote in message
...
I am attempting to create a formula that I believe needs to utilize the
Match
function. I would like to look at the values in certain cells (every 4th
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.

For example, If I had the values 5, 7, 10, 15 in cells A4, E4, I4, and M4
the formula would look for the first instance of a value above 11 (in this
case cell M4) and the result would be the conents of cell M1. How would I
accomplish this. Thank you in advance for your assistance.

Match Function
 

I'm assuming that you mean in cell a10 enter the number 11 as an array.
Correct? Thank you very much for your help on this.

"Biff" wrote:

Try this:

A10 = 11

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A1:M1,MATCH(1,(MOD(COLUMN(A4:M4),4)=1)*(A4: M4A10),0))

Biff

"SJT" wrote in message
...
I am attempting to create a formula that I believe needs to utilize the
Match
function. I would like to look at the values in certain cells (every 4th
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.

For example, If I had the values 5, 7, 10, 15 in cells A4, E4, I4, and M4
the formula would look for the first instance of a value above 11 (in this
case cell M4) and the result would be the conents of cell M1. How would I
accomplish this. Thank you in advance for your assistance.

Match Function
 

No, You enter the formula as an array.

A10 is your lookup value. You can hard code it into the formula if you want
to but it's more flexible using a cell reference.

Biff

"SJT" wrote in message
...
I'm assuming that you mean in cell a10 enter the number 11 as an array.
Correct? Thank you very much for your help on this.

"Biff" wrote:

Try this:

A10 = 11

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=INDEX(A1:M1,MATCH(1,(MOD(COLUMN(A4:M4),4)=1)*(A4: M4A10),0))

Biff

"SJT" wrote in message
...
I am attempting to create a formula that I believe needs to utilize the
Match
function. I would like to look at the values in certain cells (every
4th
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.

For example, If I had the values 5, 7, 10, 15 in cells A4, E4, I4, and
M4
the formula would look for the first instance of a value above 11 (in
this
case cell M4) and the result would be the conents of cell M1. How
would I
accomplish this. Thank you in advance for your assistance.

Match Function
 

Yeah, I realized after I sent you the reply. Thanks. I'm getting an N/A
error message (and I have entered as an array) and did insert values that
should have given me a valid result. I am using the following formula
=INDEX(X2:AS2,MATCH(1,(MOD(COLUMN(X14:AS14),7)=1)* (X14:AS14B18),0)) and the
cells in row 2 are dates, any reason I might be having a problem? I changed
the "4" to a "7" since the cells I want to evaluate are every 7th column.
Thanks again.

"Biff" wrote:

No, You enter the formula as an array.

A10 is your lookup value. You can hard code it into the formula if you want
to but it's more flexible using a cell reference.

Biff

"SJT" wrote in message
...
I'm assuming that you mean in cell a10 enter the number 11 as an array.
Correct? Thank you very much for your help on this.

"Biff" wrote:

Try this:

A10 = 11

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=INDEX(A1:M1,MATCH(1,(MOD(COLUMN(A4:M4),4)=1)*(A4: M4A10),0))

Biff

"SJT" wrote in message
...
I am attempting to create a formula that I believe needs to utilize the
Match
function. I would like to look at the values in certain cells (every
4th
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.

For example, If I had the values 5, 7, 10, 15 in cells A4, E4, I4, and
M4
the formula would look for the first instance of a value above 11 (in
this
case cell M4) and the result would be the conents of cell M1. How
would I
accomplish this. Thank you in advance for your assistance.

Match Function
 

See your later post.

Biff

"SJT" wrote in message
...
Yeah, I realized after I sent you the reply. Thanks. I'm getting an N/A
error message (and I have entered as an array) and did insert values that
should have given me a valid result. I am using the following formula
=INDEX(X2:AS2,MATCH(1,(MOD(COLUMN(X14:AS14),7)=1)* (X14:AS14B18),0)) and
the
cells in row 2 are dates, any reason I might be having a problem? I
changed
the "4" to a "7" since the cells I want to evaluate are every 7th column.
Thanks again.

"Biff" wrote:

No, You enter the formula as an array.

A10 is your lookup value. You can hard code it into the formula if you
want
to but it's more flexible using a cell reference.

Biff

"SJT" wrote in message
...
I'm assuming that you mean in cell a10 enter the number 11 as an array.
Correct? Thank you very much for your help on this.

"Biff" wrote:

Try this:

A10 = 11

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=INDEX(A1:M1,MATCH(1,(MOD(COLUMN(A4:M4),4)=1)*(A4: M4A10),0))

Biff

"SJT" wrote in message
...
I am attempting to create a formula that I believe needs to utilize
the
Match
function. I would like to look at the values in certain cells
(every
4th
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.

For example, If I had the values 5, 7, 10, 15 in cells A4, E4, I4,
and
M4
the formula would look for the first instance of a value above 11
(in
this
case cell M4) and the result would be the conents of cell M1. How
would I
accomplish this. Thank you in advance for your assistance.