lookup with two variables

[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

 

[Gary''s Student]

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="Jane Smith")*ROW(A1:A100))-1,2)

will display the correct rate.

 

[Dave Peterson]

You have a few choices...

One is to insert a new column A in the rates worksheet.

Then you could concatenate the values in the new column B and C into column A.

=b2&"|"&c2
(and drag down)

Then you could modify the =vlookup() to look at this new column--but concatenate
the year and name into the value to match:

=vlookup(a2&"|"&b2,othersheet!a:e,3,false)
where a2 holds the year and b2 holds the name.

There are other ways, too.

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))