Home |
Search |
Today's Posts |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with 2 conditions?
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)) Mayte wrote: 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% -- Dave Peterson |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to vlookup a value with certain conditions? | Excel Discussion (Misc queries) | |||
Vlookup with 2 Conditions | Excel Discussion (Misc queries) | |||
Vlookup with conditions | Excel Worksheet Functions | |||
VLOOKUP w/ 2 or more conditions | Excel Worksheet Functions | |||
Vlookup using 2 conditions | Excel Discussion (Misc queries) |