Excel 2010- Need formula to compare 2 columns on 2 sheets and return value of third cell 1

[jlr123]

Compare 2 cells a1:b1 sheet 1 with 2 cells a1:b1 on sheet 2 and return value of c1if matched

 

[Andrzejek]

How about formula in cell D1 on Sheet1:
[tt]
=IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1), C1, "")
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[combo]

Use IF function with AND function in condition to have both matches in compared ranges. You need to decide what you return if no match.

combo

 

[jlr123]

Ok, I am including my workbook. The formula did not work for me. Thanks so much for your assistance!

 

[SkipVought]

Add a HELPER column to sheet 2, that concatenates the value in column A & B.

The formula in sheet 1 column D
[tt]
D3: =IFERROR(INDEX(Sheet2!C:C,MATCH(Sheet1!A3&Sheet1!B3,Sheet2!D,0),1),"NO MATCH")
[/tt]

Your workbook returned with additions and solution.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jlr123]

Thanks so much Skip! Works beautifully.

 

[SkipVought]

FYI I really didn’t answer YOUR question.

When you produced your workbook, I tried your approch, comparing “2 cells a1:b1 sheet1 with 2 cells a1:b1 sheet2” I got none that matched.

So I assumed that you intended something else, tried that something else and got a significant number of matches. That something else is known as a LOOKUP.: lookup some value in a range.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Skip - how do you do this?
One of my customer wanted to send me to the "Mind Reading Class", which would be very useful in my job. Unfortunately, I could not find such an offer in any of the educational institutions.
Where did you get yours....?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

@Andy, I’ve spent a considerable period of time “listening” to people’s descriptions of what they think they want, asking the requisite questions and distilling these to get the desired proof: the actual requirements. I nearly always ask questions to verify that we are on the same page.

I doubted the OP’s initial statement and when they finally provided the workbook, I did as I previously stated, made a guess and it was, in this instance, correct.

I guess its a matter of experience. I’m not always right. But when one method gives ZERO results, then look for the next best approch.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}