Index Indirect and Match Formula Question

[petevang]

Any idea why my formula isn't working exactly. The formula I'm using is

=INDEX(INDIRECT("Term"&G2),MATCH(H2,INDEX(INDIRECT("Term"&G2),0,1)),MATCH(I2,INDEX(INDIRECT("Term"&G2),1,0)))

I'd like to be able to have the formual return a APR for the selected term, LTV, and credit band

Any help is appreciated!!!

[ConneXionLost]

You seem to be missing the "!" character in your cell references. Try using "Term!" instead of "Term".

[daddylonglegs]

I think Term36 is a range name not a reference so you don't need !.....

Firstly if you use MATCH with no 3rd argument you need the list to be sorted ascending, that doesn't apply to the top rows of your tables.

How do you expect the match to work, should 701 match with the nearest number or the next lowest or next highest? Do the percentages work in the same way?

[petevang]

Thanks for your reply.....I was hoping to be able t enter data into G2:I2 and have it scan the 5 tables and deliver the closest match. The vertical axis should display the lower boundary, but I just realized it shows the upper boundary. The horizontal axis show the lower boundary. 701 should match to the next highest number.

[daddylonglegs]

Try adding a -1 argument to the 2nd MATCH like this

=INDEX(INDIRECT("Term"&G2),MATCH(H2,INDEX(INDIRECT("Term"&G2),0,1)),MATCH(I2,INDEX(INDIRECT("Term"&G2),1,0),-1))

does that work as you want?

[NBVC]

or try reversing the score range labels so the smallest is left most... then you can use your original formula as we created here:

http://www.excelforum.com/excel-prog...-question.html

[petevang]

I went ahead and changed reversed the score ranges so that they read from least to greatest. I realized that the vertical axis states max amounts. Would you suggest I change this column to reflect minimums. What I'd like to see is when I'm looking for Term 36, LTV 103%, and credit score 665 a returned rate of 7.49%. Right now it shows 6.99%. Any suggestions? Thanks again!!

[daddylonglegs]

Yes, MATCH with no 3rd argument expects a list in ascending order and the match is with the greatest value lower than or equal to the lookup value.....so with a list like 500, 550, 600, 650, 700 then 699 will match with 650....so yes, the amounts should represent the lower bound of each range