Excel: match two cells in one sheet to two cells in another and return a third cells value

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&Sheet1!B1:B100,0))

Array entered.

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

Biff

"Spence" <[email protected]> wrote in message
news:[email protected]...
>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
>