Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup - multiple lookup values
I have 2 spreadsheets. Each has an inventory number, a security
number and the second sheet has a duration. Each set of inventory number and security number are on the same row of the spreadsheet. The sheets don't match each other, though. I'm struggling with how to create either an if/then or a vlookup formula in which I say: If (inventory number = inventory number) and (security number = security number), then populate duration in a new cell. I've got an if/then to work but it obviously relies on the pairs being on the same row of both spreadsheet. I'm stuck with how to incorporate both criteria into a vlookup formula. Thanks for any help you can provide. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup - multiple lookup values
Assume data is in columns A,B and C (Inventory number, security number and
duration): in Sheet1, column C: =SUMPRODUCT(--(sheet2!A2:A100=A2),--(sheet2!B2:B100=B2),(sheet2!C2:C100)) copy down as required. HTH "kec01" wrote: I have 2 spreadsheets. Each has an inventory number, a security number and the second sheet has a duration. Each set of inventory number and security number are on the same row of the spreadsheet. The sheets don't match each other, though. I'm struggling with how to create either an if/then or a vlookup formula in which I say: If (inventory number = inventory number) and (security number = security number), then populate duration in a new cell. I've got an if/then to work but it obviously relies on the pairs being on the same row of both spreadsheet. I'm stuck with how to incorporate both criteria into a vlookup formula. Thanks for any help you can provide. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup - multiple lookup values
On Mar 13, 2:06 pm, Toppers wrote:
Assume data is in columns A,B and C (Inventory number, security number and duration): in Sheet1, column C: =SUMPRODUCT(--(sheet2!A2:A100=A2),--(sheet2!B2:B100=B2),(sheet2!C2:C100)) copy down as required. HTH "kec01" wrote: I have 2 spreadsheets. Each has an inventory number, a security number and the second sheet has a duration. Each set of inventory number and security number are on the same row of the spreadsheet. The sheets don't match each other, though. I'm struggling with how to create either an if/then or a vlookup formula in which I say: If (inventory number = inventory number) and (security number = security number), then populate duration in a new cell. I've got an if/then to work but it obviously relies on the pairs being on the same row of both spreadsheet. I'm stuck with how to incorporate both criteria into a vlookup formula. Thanks for any help you can provide.- Hide quoted text - - Show quoted text - I'm a real novice at this so my next question may be elementary. What are the 2 short hyphens before the 2 instances of (sheet2? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup - multiple lookup values
The "double unary minus" converts a logical TRUE or FALSE to a number 1 or
0, so that it can be multiplied in the SUMPRODUCT function. http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- David Biddulph "kec01" wrote in message oups.com... On Mar 13, 2:06 pm, Toppers wrote: Assume data is in columns A,B and C (Inventory number, security number and duration): in Sheet1, column C: =SUMPRODUCT(--(sheet2!A2:A100=A2),--(sheet2!B2:B100=B2),(sheet2!C2:C100)) copy down as required. HTH "kec01" wrote: I have 2 spreadsheets. Each has an inventory number, a security number and the second sheet has a duration. Each set of inventory number and security number are on the same row of the spreadsheet. The sheets don't match each other, though. I'm struggling with how to create either an if/then or a vlookup formula in which I say: If (inventory number = inventory number) and (security number = security number), then populate duration in a new cell. I've got an if/then to work but it obviously relies on the pairs being on the same row of both spreadsheet. I'm stuck with how to incorporate both criteria into a vlookup formula. Thanks for any help you can provide.- Hide quoted text - - Show quoted text - I'm a real novice at this so my next question may be elementary. What are the 2 short hyphens before the 2 instances of (sheet2? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup(?) with 2 Lookup Values | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
How do I use vlookup with two lookup values? | Excel Worksheet Functions | |||
vlookup using two lookup values? | Excel Worksheet Functions | |||
Vlookup based on two lookup values | Excel Worksheet Functions |