OFFSET function not working

**Cicada** · 04-17-2012, 07:04 AM

Hello all i am trying to figure out why the following is not working that is not returning the correct value

=OFFSET(A5,MATCH(LARGE(J5:J10,4),J:J,0)-9,0)

though strangely in the same table the following does work

=OFFSET(A5,MATCH(LARGE(F5:F10,4),F:F,0)-5,0)

Any help greatly appreciated

Regards
Dan

**Andrew-R** · 04-17-2012, 07:09 AM

Why have you changed the -5 to -9 in the first formula?

The MATCH statement is finding the row in column J which contains the largest value of the range J5:J10. If we assume that this value is in J5 it will return a value of 5 (or 6 if the largest value is in J6, 7 if it's in J7 and so on), so you're offsetting A5 by that number of rows minus either 9 or 5.

If you want the corresponding value from the A column then you need to subtract 5 from that value, so that if the result is found in J5 then the value in cell A5 will be returned (A5 offset by 0 rows).