vlookup with 2 conditions?

[Mayte]

hi-
i need to return a value based on 2 conditions... i have a list of 500+
names, they all have a unique identifier (employee id) so i need to get
different values/scores from tab 2 into tab 1.

so, for john i'd have his score from category 1 (80%) in tab 1 C2 and his
score for category 2 (20%) in D2 and son on.

i did a simple v-lookup
=(VLOOKUP(C$2,Tab 2!$A:$C,3,FALSE))
BUT it gives me the first score it finds that matches the category name and
I need to score for each category for each employee... any ideas???

tab 1:
A B C D E
name - id - category 1 - category 2 - category 3
john 123
tim 456
steven 789

tab 2:
A B C
categories - id - score
category 1 123 80%
category 2 123 20%
category 2 456 30%
category 3 123 5%
category 1 789 30%

 

[Monkyo]

you can create a new column with a unique ID in tab 2. for example,
category1_123 80%
category2_120 20%.
you can just do A2&B2 to create a unique ID (this will really be a new
column a with formula B2&C2

then when you vlookup, just vlookup on the unqiue ID:
vlookup(category1_123, TABLE, COLUMN, 0)

You can then make the vlookkup dynamic by doing
vlookuP(c$2&$A$3,Tab 2!$A:$C,3,FALSE))

hope this helps and makes sense

 

[Dave Peterson]

Saved from a previous post:

You have a few choices...

One is to insert a new column A in the table 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.

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))