I want to reference cells in two columns in sheet1 and when the
criteria matches cells in two columns of sheet2 I want to return the
value in the third column of sheet1 to the third column of sheet2?

Does that make sense? Vlookup won't work here because I want to match
two columns in the same array. I thought Indexing would work but I
keep coming up with an error. I also tried to do a Match/Offset
formula from Chip Pearson's website but that wasn't what I was looking
for. Any help is much appreciated.

Thanks,
Spence

using ranges of b2:d6 in both sheet 1 and sheet2, I used this formula in
sheet 2, cell d2

=SUMPRODUCT(--(Sheet1!$B$2:$B$6=Sheet2!B2),--(Sheet1!$C$2:$C$6=Sheet2!C2),--(Sheet1!$D$2:$D$6))

hth,
dave

"Spence" wrote:

I want to reference cells in two columns in sheet1 and when the
criteria matches cells in two columns of sheet2 I want to return the
value in the third column of sheet1 to the third column of sheet2?

Does that make sense? Vlookup won't work here because I want to match
two columns in the same array. I thought Indexing would work but I
keep coming up with an error. I also tried to do a Match/Offset
formula from Chip Pearson's website but that wasn't what I was looking
for. Any help is much appreciated.

Thanks,
Spence

Hi!

Can you post an example?

I'll bet you want something like this:

=INDEX(third column of sheet1,MATCH(two columns of sheet2,two columns in
sheet1,0))

If you had said where all this stuff is specifically located, then the
formula would look something like this:

=INDEX(Sheet1!C1:C100,MATCH(A1&B1,Sheet1!A1:A100&S heet1!B1:B100,0))

Array entered.

When you include details we don't have to guess!

Biff

"Spence" wrote in message
oups.com...
I want to reference cells in two columns in sheet1 and when the
criteria matches cells in two columns of sheet2 I want to return the
value in the third column of sheet1 to the third column of sheet2?

Does that make sense? Vlookup won't work here because I want to match
two columns in the same array. I thought Indexing would work but I
keep coming up with an error. I also tried to do a Match/Offset
formula from Chip Pearson's website but that wasn't what I was looking
for. Any help is much appreciated.

Thanks,
Spence

Hi All,

My question is simple. below is the table. I want to match two cells in first set of data with the second set of data and return the value of Column "Job Status" in first set from the second set data.
First Set
Date Aired Home Team Away Team Job Status
23-Oct-10 Dallas Stars Nashville Predators
7-Oct-10 Minnesota Wild Carolina Hurricanes
7-Oct-10 Toronto Maple Leafs Montreal Canadiens
7-Oct-10 Pittsburgh Penguins Philadelphia Flyers


Second Set


Date Home Team Away Team Job Status
23-Oct-10 Dallas Stars Nashville Predators Upload Complete
26-Oct-10 Dallas Stars Anaheim Ducks Upload Complete
30-Oct-10 Dallas Stars Buffalo Sabres Upload Complete
3-Nov-10 Dallas Stars Pittsburgh Penguins Upload Complete
1-Feb-11 Dallas Stars Vancouver Canucks VD Created


Please mail me the solution for this :

Quote:

Originally Posted by Biff Hi! Can you post an example? I'll bet you want something like this: =INDEX(third column of sheet1,MATCH(two columns of sheet2,two columns in sheet1,0)) If you had said where all this stuff is specifically located, then the formula would look something like this: =INDEX(Sheet1!C1:C100,MATCH(A1&B1,Sheet1!A1:A100&S heet1!B1:B100,0)) Array entered. When you include details we don't have to guess! Biff "Spence" wrote in message oups.com... I want to reference cells in two columns in sheet1 and when the criteria matches cells in two columns of sheet2 I want to return the value in the third column of sheet1 to the third column of sheet2? Does that make sense? Vlookup won't work here because I want to match two columns in the same array. I thought Indexing would work but I keep coming up with an error. I also tried to do a Match/Offset formula from Chip Pearson's website but that wasn't what I was looking for. Any help is much appreciated. Thanks, Spence