Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to look up multiple fields?
Hello,
Is VLOOKUP function capable of looking at two criteria in the fields and return the value? If yes, how to put in the code or if not is there any other way to achieve this? Thanks Sample: Sheet1 - data sheet (RESULT column is the vlookup) Part# Oper RESULT ABC1 20 50 ABC1 10 30 ABC3 10 30 ABC4 60 40 ABC4 70 100 sheet2 - Reference sheet Part# Oper Time ABC1 10 30 ABC1 20 50 ABC3 10 30 ABC4 60 40 ABC4 70 100 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to look up multiple fields?
I don't think VLOOKUP can do it but you can CONCATENATE the 2 values together
(should create a unique value) in a new column then do your VLOOKUP. -- Yanick "Cam" wrote: Hello, Is VLOOKUP function capable of looking at two criteria in the fields and return the value? If yes, how to put in the code or if not is there any other way to achieve this? Thanks Sample: Sheet1 - data sheet (RESULT column is the vlookup) Part# Oper RESULT ABC1 20 50 ABC1 10 30 ABC3 10 30 ABC4 60 40 ABC4 70 100 sheet2 - Reference sheet Part# Oper Time ABC1 10 30 ABC1 20 50 ABC3 10 30 ABC4 60 40 ABC4 70 100 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to look up multiple fields?
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)) Cam wrote: Hello, Is VLOOKUP function capable of looking at two criteria in the fields and return the value? If yes, how to put in the code or if not is there any other way to achieve this? Thanks Sample: Sheet1 - data sheet (RESULT column is the vlookup) Part# Oper RESULT ABC1 20 50 ABC1 10 30 ABC3 10 30 ABC4 60 40 ABC4 70 100 sheet2 - Reference sheet Part# Oper Time ABC1 10 30 ABC1 20 50 ABC3 10 30 ABC4 60 40 ABC4 70 100 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to look up multiple fields?
If the value you want to return is numeric, you could use SUMPRODUCT:
=SUMPRODUCT(--(Sheet2!$A$2:$A$6=Sheet1!A2),--(Sheet2!$B$2:$B$6=Sheet1!B2),Sheet2!$C$2:$C$6) In this example, I entered your sample data on Sheet1 and Sheet2 starting in cell A1. I entered the formula above into cell C2 on Sheet1 and copied down. The range of rows referenced on Sheet2 needs to be the same for every column. Adjust the range of rows to fit your data, but you can't reference whole columns with SUMPRODUCT. Hope this helps, Hutch "Yanick" wrote: I don't think VLOOKUP can do it but you can CONCATENATE the 2 values together (should create a unique value) in a new column then do your VLOOKUP. -- Yanick "Cam" wrote: Hello, Is VLOOKUP function capable of looking at two criteria in the fields and return the value? If yes, how to put in the code or if not is there any other way to achieve this? Thanks Sample: Sheet1 - data sheet (RESULT column is the vlookup) Part# Oper RESULT ABC1 20 50 ABC1 10 30 ABC3 10 30 ABC4 60 40 ABC4 70 100 sheet2 - Reference sheet Part# Oper Time ABC1 10 30 ABC1 20 50 ABC3 10 30 ABC4 60 40 ABC4 70 100 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to look up multiple fields?
Here's one way. If the Part#-Oper combinations are mutually exclusive (no
duplicates) in your Sheet1 database, you can use SUMPRODUCT in Sheet2 as a lookup. Assuming a Sheet1 database of A1..C100, in Sheet2, cell C2, enter SUMPRODUCT((A2=Sheet1!$A$2:$A$100)*(B2=Sheet1!$B$2 :$B$100)*(Sheet1!$C$2:$C$100)) Copy down. "Cam" wrote: Hello, Is VLOOKUP function capable of looking at two criteria in the fields and return the value? If yes, how to put in the code or if not is there any other way to achieve this? Thanks Sample: Sheet1 - data sheet (RESULT column is the vlookup) Part# Oper RESULT ABC1 20 50 ABC1 10 30 ABC3 10 30 ABC4 60 40 ABC4 70 100 sheet2 - Reference sheet Part# Oper Time ABC1 10 30 ABC1 20 50 ABC3 10 30 ABC4 60 40 ABC4 70 100 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to look up multiple fields?
Try one of these:
This is an array formula**: =INDEX(Sheet2!C$2:C$6,MATCH(1,(Sheet2!A$2:A$6=A2)* (Sheet2!B$2:B$6=B2),0)) Copied down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) This formula is normally entered but will only work if the combination of lookup values are unique: =SUMPRODUCT(--(Sheet2!A$2:A$6=A2),--(Sheet2!B$2:B$6=B2),Sheet2!C$2:C$6) Copied down as needed -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hello, Is VLOOKUP function capable of looking at two criteria in the fields and return the value? If yes, how to put in the code or if not is there any other way to achieve this? Thanks Sample: Sheet1 - data sheet (RESULT column is the vlookup) Part# Oper RESULT ABC1 20 50 ABC1 10 30 ABC3 10 30 ABC4 60 40 ABC4 70 100 sheet2 - Reference sheet Part# Oper Time ABC1 10 30 ABC1 20 50 ABC3 10 30 ABC4 60 40 ABC4 70 100 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Fields In Multiple Ranged Pivot Table | Excel Discussion (Misc queries) | |||
vlookup 2 fields | Excel Worksheet Functions | |||
vlookup 2 fields | Excel Worksheet Functions | |||
how do I add multiple fields together? | Excel Worksheet Functions | |||
Vlookup for non-unique fields | Excel Discussion (Misc queries) |