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.

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.

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?

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?