Lookup a value in a table having entered 2 key values. INDEX ?

**robjudge** · 04-14-2008, 01:02 PM

Hi,

I have a table of money rates. The monthly rate depends upon
1. how long a loan is taken out and
2. how much is borrowed.

The user has to enter these 2 bits of info before excel examines a table selects a rate. A simple formula (which even I could do!?) calculates the monthly payment.

Please see the attached. I nearly got there using INDEX, but the trouble is that the value column contains a range rather than a fixed value.

I would be most grateful for a solution.

Thanks in advance.

Rob

**NBVC** · 04-14-2008, 01:30 PM

tRY:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER instead of just ENTER.. you will see { } brackets appear around the formula.

The formula could be less complex if you change your column and row titles to be just numbers (rows - start numbers, column year numbers)

For instance, if you entered just the numbers 1 - 5 for the years and you entered 2000, 5000, 15000 for the Capital Values, then the formula would be

Please Login or Register  to view this content.

normally entered.

**martindwilson** · 04-14-2008, 02:04 PM

for such a small table
try this put amount in b2 years in c2
d2 and e2 return results(d2 and e2 are conditionaly formatted to change text to the same color as cell ,white,if nothing is in b2 or c2)
its a lazy way but quick

**robjudge** · 04-14-2008, 03:48 PM

Awsome NBVC - I would never have dreamt of the 1st formula! The 2nd is ample, as I will hide the rates on a 2nd sheet, and the user will just enter the years and value and be presented with the rate. - I have now learnt how the "match type" in MATCH works. Ready for next time perhaps!?

Martinandwilson - thank you as well, but I could not download your file for some reason.

Best regards
Rob