Lookup Match

[LookupTroubles]

I have a file that calculates bonuses based on multiple bonus plans. Right
now I have a vlookup going to a specific spot in another tab that matches up
to correct bonus plan, but it's very prone to error if I copy a line down and
the new person is on a different plan and I forget to change the lines. I
didn't know if there was a way with match or index to set it up to find the
right plan, but still do a lookup.

Below is how I have it set up now

Calculation Spreadsheet

Column A Col B Col C ColD Col E
Empl A Plan A 10% =If(C1>0,hlookup(c1+.5,bonus
sheet!1:3,2,false),0)
Empl B Plan B 15% =If(C2>0,hookup(c2+.5,bonus sheet!1:3,3,false),0)

Bonus Sheet
Column A Colmn B Column C Column D Column E
0 5% 10% 15%
Plan A 1% 2% 3% 4%
Plan B 5% 10% 15% 20%

So D1 in Bonus sheet would return 3% and D2 would return 20% -- but do you
see my problem if I forget to change the hlookup line on employee B I'll get
the wrong % -- Does anyone know a better way to get this done?

Thanks in advance for your help.

 

[Bob Phillips]

Try this

=INDEX(Bonus!$A$1:$E$20,MATCH(B2,Bonus!$A:$A,0),MATCH(C2,Bonus!$1:$1,0))