Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Condition Vlookup
I have two worksheets that contain similar information. What I need is a
formula that will lookup the Value of column A in worksheet one, but also theres a second condition that I want it to meet which is in column B. So basically if these two conditions are met then I want to see the result from using worksheet 2 as the lookup reference table. Does anyone know the correct format for a two condition Vlookup? Any help is greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Condition Vlookup
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)) Santi wrote: I have two worksheets that contain similar information. What I need is a formula that will lookup the Value of column A in worksheet one, but also theres a second condition that I want it to meet which is in column B. So basically if these two conditions are met then I want to see the result from using worksheet 2 as the lookup reference table. Does anyone know the correct format for a two condition Vlookup? Any help is greatly appreciated. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Condition | Excel Discussion (Misc queries) | |||
two condition for vlookup? | Excel Worksheet Functions | |||
2 condition vlookup | Excel Discussion (Misc queries) | |||
VLOOKUP used only when IF condition is met | Excel Worksheet Functions | |||
vlookup in two condition | Excel Discussion (Misc queries) |