Two dimensional Table lookup with Columns and row headings as ranges

[dwayne.fernandes@gmail.com]

Hi I am Dwayne..Just joine the forum..happy to be a part...


I have a question....


I have a two dimensional table below of days and amounts invested and the interest rates for asssociated days and amount.For example in row 1 and column 1 amount invested from 0 to 10 million for 7 to 10 days will fetch a return of 7.25%

AMOUNTS

'DAYS '0-10 '10-25 '25-50 '50-100

7-10 '7.25 7.25 7.25 7.25

11-14 7.5 7.5 7.5 7.5

15-30 7.6 7.7 7.7 7.7

31-60 7.7 7.7 7.7 7.7

61-90 7.7 7.8 7.7 7.7

91-180 7.7 7.7 7.8 7.7

180-365 7.7 7.7 7.7 7.9

366-730 7.8 7.8 7.8 7.8

730-1096 8 8 8 8

1096-1461 8' 8' 8' 8'


I have a list of amounts invested and the number of days in columns

'Amount 'Days 'Rate

20 40

280 63

65 300'

I have to lookup the amount(20) and days(40) in the previous table and return the rate.Can anyone guide me on how this can be done?

 

[Faseeh]

Hi dwayne.fernandes,


Welcome to the forum!!


There might be some better way of doing this but i like this one:


http://img.chandoo.org/faseeh/ColRowDoubleLookupTable.xlsx


Regards,

Faseeh

 

[SirJB7]

Hi, dwayne.fernandes@gmail.com!

May I suggest you to change your nick name? It's too long and it overlaps the comment area making it unreadable. If you want to keep your email address you can put it in the website field of your profile, then when clicking on you nick on any comment the user will be led to your email.

Regards!

 

[dwayne.fernandes@gmail.com]

thanks a lot Faseeh. thats was great..how do u attach an excel file in a post?

 

[dwayne.fernandes@gmail.com]

thanks SirJB7 i have made the change

 

[Faseeh]

Hi Dwayne,


You are welcome Dwayne! You can upload your file to any online file storage site, i will recommend you to use Dropbox and made file rights "Public" so that ever one can download it, paste the link here. That all.


Take Care

Faseeh

 

[SirJB7]

Hi, Dwayne!


Assuming that amounts are in row 2 from B2:E2, that days are in column 1 from A3:A12, and that rates are in cells B3:E12, try this for Amount in G2 and Days in H2:

=INDICE($B$3:$E$12;COINCIDIR(H2;$A$3:$A$12;1);COINCIDIR(G2;$B$2:$E$2;1)) -----> in english: =INDEX($B$3:$E$12,MATCH(H2,$A$3:$A$12,1),MATCH(G2,$B$2:$E$2,1))


Regards!