Forum Discussion
Solved
I need a formula for this
please see attached I am trying to get in "cell J1" the bonus amount from column c based on cell "j10" column "A" ''B'' see I put in an index Match formula excel returned 16500 instead of 17,000 ...
Use
=INDEX(C2:C124,MATCH(J10,A2:A124))
or
=VLOOKUP(J10, A2:C124, 3)
1) We look at the lower bound of each tier instead of the upper bound, i.e. column A instead of column B.
2) The range in column C must have the same size as the range in column A.
3) Removing 0 as third argument of MATCH allows for an approximate match instead of an exact match.
Since column A on Sheet2 is formatted as Text, cell M4 on Sheet1 should be formatted as Text too.
The formula in N4 is
=INDEX(Sheet2!B3:R88,MATCH(M4,Sheet2!A3:A88,0),MATCH(J1,Sheet2!B2:R2,0))
See the attached version.
Its working Thx