I'm getting an unexpected result from an OFFSET(INDEX(MAX(LARGE))) formula. I've split the whole formula into its constituents to find MATCH is returning a result I don't expect.
Given the LARGE formula correctly identifies 44927 as the third largest number, I expected MATCH to return a result of 2, being that 44927 is in the 2nd column of the 1st row of the data. Where am I going wrong?
Given the LARGE formula correctly identifies 44927 as the third largest number, I expected MATCH to return a result of 2, being that 44927 is in the 2nd column of the 1st row of the data. Where am I going wrong?
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
1000 | 44927 | 250 | 45107 | 500 | 45108 | ||||
Result | Formula | Intention | |||||||
44927 | =LARGE(A2:J2,3) | 3rd largest number | |||||||
5 | =MATCH(LARGE(A2:J2,3),A2:J2) | Position of 44927 (should return 2) | |||||||
500 | =INDEX(A2:J2,MATCH(LARGE(A2:J2,3),A2:J2)) | should return 44927 or R1C2 | |||||||
45107 | =OFFSET(INDEX(A2:J2,MATCH(LARGE(A2:J2,3),A2:J2)),0,-1) | should retun value at R1C1 = 1000 | |||||||