....enter with Ctrl+Shift+Enter (array formula) and copy down.


There is no error checking so you will get #NA if no match so you might
prefer Pete's approach.

"Toppers" wrote:

This does a compare by taking the Vendor name and Invoice Key from Sheet1 and
matching against Sheet2 and compares values:


=IF(INDEX(Sheet1!D2:D5,MATCH(1,(Sheet1!A3=Sheet1!A 2:A5)*(Sheet1!B3=Sheet1!B2:B5),0))=INDEX(Sheet2!D2 :D5,MATCH(1,(Sheet1!A3=Sheet2!A2:A5)*(Sheet1!B3=Sh eet2!B2:B5),0)),"OK","NOK")

You could extend this to include PO number:

=IF(INDEX(Sheet1!D2:D5,MATCH(1,(Sheet1!A3=Sheet1!A 2:A5)*(Sheet1!B3=Sheet1!B2:B5)*(sheet1!C3=Sheet1!C 2:C5),0))=INDEX(Sheet2!D2:D5,MATCH(1,(Sheet1!A3=Sh eet2!A2:A5)*(Sheet1!B3=Sheet2!B2:B5))*(sheet1!C3=S heet2!C2:C5),0)),"OK","Not OK")

HTH

"Cecilia" wrote:

Hello, I am trying to comparing two worksheets with heavy data volume and
digging out the duplicated entries between the two worksheets. For example,

Worksheet 1
Vendor name Invoice Key PO Number Amount
1 ABC ER006 B0045900 $10,000
2 GHI PC001 B0050001 $12,000
3 XYZ ER009 B0049299 $12,999
4 WW PC005 B0005200 $54,000

Worksheet 2
Vendor name Invoice Key PO Number Amount
1 BBC PC005 B0005000 $50,000
2 DF PC010 B0060010 $999.99
3 GHI PC001 B0050001 $12,000
4 GHI PC002 B0050001 $5,000

The finding should be that the GHI transaction in the amount of $12,000 is
on both worksheets. (#2 on Worksheet 1 matches #3 on Worksheet 2) It would be
a tidious task and consumes a lot of time if I do it mannually. Is there a
way I can do it much faster with Excel's help?

I'd appreciate your suggestions and comments.