Excel - vlookup with 2 conditions?

Asked By Mayte on 22-Mar-10 05:22 PM
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 replied to Mayte on 22-Mar-10 06:00 PM
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 replied to Mayte on 22-Mar-10 06:14 PM
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.  (do not 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 are 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))


--

Dave Peterson