Match Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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:M4>A10),0))

Biff
 
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.
 
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
 
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:AS14>B18),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.
 
See your later post.

Biff

SJT said:
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:AS14>B18),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.
Click to expand...
 
Back
Top