As you are aware, if there is only a single criterium and the criteria column
is on the left, you can use VLOOKUP() to get associated items on the same
row. If the criteria row in in the middle, use can use MATCH() to get the
row number and INDEX() or OFFSET() to get the associated items.

Here is a neat trick. You can use SUMPRODUCT() to get the row number with
multiple criteria. For example:

=OFFSET(A1,SUMPRODUCT((A1:A100=2007)*(B1:B100="Jan e Smith")*ROW(A1:A100))-1,2)

will display the correct rate.
--
Gary''s Student - gsnu201001


"Mayasmom" wrote:

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