Set up up table to look similar to this and between the term of the loans and
the table value add a column and insert a row number (1,2,3,4....). Do the
same thing for the loan amount. You will use the row numbers in the formula
below:

=INDEX(C4:G8,VLOOKUP(C11,A4:B8,2),HLOOKUP(D11,C2:G 3,2))

C11 and D11 are your value that you are going to lookup in the grid.

Loan
6,000 10,000 14,000 18,000 22,000
Term 1 2 3 4 5
24 1 24 40 56 72 88
36 2 30 65 100 135 170
48 3 36 90 144 198 252
60 4 42 115 188 261 334
72 5 48 140 232 324 416


HTH

"KDD" wrote:

I have a sales incentive grid with tenure of loan on one axis & loan size on
another axis. For e.g., for a loan size between $6000 to $9999 at a tenor of
24 months, teh incentive payable is $24 whereas for a loan size between
$10000 to &19999 at a tenor of 36 months, the incentive payable is $65. There
are a total of 7 loan bands on one axis and 5 tenor bands on the other axis.

How do i create a formula so that excel picks the correct incentive amount
depending upon the loan amount and tenor combination.

Its so confusing. Pls help..