Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup Match
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(C10,hlookup(c1+.5,bonus sheet!1:3,2,false),0) Empl B Plan B 15% =If(C20,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. |
#2
|
|||
|
|||
Try this
=INDEX(Bonus!$A$1:$E$20,MATCH(B2,Bonus!$A:$A,0),MA TCH(C2,Bonus!$1:$1,0)) -- HTH Bob Phillips "LookupTroubles" wrote in message ... 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(C10,hlookup(c1+.5,bonus sheet!1:3,2,false),0) Empl B Plan B 15% =If(C20,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup & match | Excel Discussion (Misc queries) | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions |