I am trying to identify the correct rate with 4 different varialbes and a range. I have tried to use the index function and I am not able to find the correct rate from the matrix when the criteria is in a range.
Any help would be greatly appreciated.
Thanks,
John
Last edited by jmgoldjr; 03-11-2013 at 09:00 PM.
What is the rule for matching the year?
In the upper table there is 1 column for the year and in the bottom table there are 2 columns for the year.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thats one of the parts I am missing. In the upper table the year is the variable I have to match for the bottom table the 2 columns for the year is the range to match from the upper table
Here are the results I get:
E2: 2.35%
E3: 2.70%
E4: 2.23%
E5: 2.23%
I don't know if those are the results you expect, though!
This array formula** entered in E2 and copied down:
=INDEX(F$11:H$15,MATCH(1,IF(B2<=B$11:B$15,IF(B2>=C$11:C$15,IF(D2>=D$11:D$15,IF(D2<=E$11:E$15,1)))),0),MATCH(1,IF(C2>=F$10:H$10,IF(C2<=F$9:H$9,1)),0))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Tony,
This is working great, I think there is one more step I am missing and that is in colume A. I have attached an updated file with your formula entered. I attempted to add the information from column A and now get a error. I am almost there and really appreciate your assistance.
Please take a look at the updated file.
Try this...
=INDEX(F$11:H$20,MATCH(1,IF(A$11:A$20=A2,IF(B2<=B$11:B$20,IF(B2>=C$11:C$20,IF(D2>=D$11:D$20,IF(D2<=E$11:E$20,1))))),0),MATCH(1,IF(C2>=F$10:H$10,IF(C2<=F$9:H$9,1)),0))
Still array entered!
That did it! Thank you for your assistance, you just saved me hours of manual entry!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks