M
Mayasmom
I'm at my witts end. I am trying to write an "if" and "vlookup" formula
using two criterias. For example, I have a monthly worksheet that I need to
populate a %rate for primary and secondary. Some Employees have secondary
and some do not. Each employee has a different rate for a primary and
secondary for each year. So I want to write a formula that would look up the
employees name and from the rate table look at their name and year and grab
that %age rate. For example, If I was to look up Jane Smith' primary rate
for 2007, if I look at the table, it will give me 2%. How do I create the
formula. Any suggestions are welcome. Thank you.
Monthly Worksheet
TCV Year Primary Secondary Primary % Secondary %
2007 Jane Smith Sam Steele
2010 Clark Higgins Sam Soul
2009 Sam Soul
2009 Sam Steele
Rates Table
Year Employee Primary % Secondary %
2007 Jane Smith 2.0% N/A
2008 Jane Smith 2.0% N/A
2009 Jane Smith 3.0% N/A
2010 Jane Smith 4.0% N/A
2007 Clark Higgins 1.0% N/A
2008 Clark Higgins 1.5% N/A
2009 Clark Higgins 2.0% N/A
2010 Clark Higgins 3.0% N/A
2007 Sam Soul 2.0% 1.0%
2008 Sam Soul 3.0% 1.5%
2009 Sam Soul 4.0% 2.0%
2010 Sam Soul 4.5% 3.0%
2007 Sam Steele 2.0% 1.5%
2008 Sam Steele 3.5% 2.0%
2009 Sam Steele 4.0% 2.5%
2010 Sam Steele 5.0% 3.5%
Monthly Worksheet
TCV Year Primary Secondary Primary % Secondary %
2007 Jane Smith Sam Steele
2010 Clark Higgins Sam Soul
2009 Sam Soul
2009 Sam Steele
using two criterias. For example, I have a monthly worksheet that I need to
populate a %rate for primary and secondary. Some Employees have secondary
and some do not. Each employee has a different rate for a primary and
secondary for each year. So I want to write a formula that would look up the
employees name and from the rate table look at their name and year and grab
that %age rate. For example, If I was to look up Jane Smith' primary rate
for 2007, if I look at the table, it will give me 2%. How do I create the
formula. Any suggestions are welcome. Thank you.
Monthly Worksheet
TCV Year Primary Secondary Primary % Secondary %
2007 Jane Smith Sam Steele
2010 Clark Higgins Sam Soul
2009 Sam Soul
2009 Sam Steele
Rates Table
Year Employee Primary % Secondary %
2007 Jane Smith 2.0% N/A
2008 Jane Smith 2.0% N/A
2009 Jane Smith 3.0% N/A
2010 Jane Smith 4.0% N/A
2007 Clark Higgins 1.0% N/A
2008 Clark Higgins 1.5% N/A
2009 Clark Higgins 2.0% N/A
2010 Clark Higgins 3.0% N/A
2007 Sam Soul 2.0% 1.0%
2008 Sam Soul 3.0% 1.5%
2009 Sam Soul 4.0% 2.0%
2010 Sam Soul 4.5% 3.0%
2007 Sam Steele 2.0% 1.5%
2008 Sam Steele 3.5% 2.0%
2009 Sam Steele 4.0% 2.5%
2010 Sam Steele 5.0% 3.5%
Monthly Worksheet
TCV Year Primary Secondary Primary % Secondary %
2007 Jane Smith Sam Steele
2010 Clark Higgins Sam Soul
2009 Sam Soul
2009 Sam Steele