Compare Sheets values in two colums
 

I have two sheets and want to match them together.
Example:
Sheet1.
A C
12 555
19 666

Sheet2

A C
12 555
22 333
I want to match if all numbers in Sheet2 are in Sheet1.
I am using =IF((SUMPRODUCT(--('Oracle viðföng'!$C$2:$C$1060=A3);--('Oracle
viðföng'!$E$2:$E$1060=C3);'Oracle
viðföng'!$J$2:$J$1060)=0);IF(OR(SUMPRODUCT(--('Oracle
viðföng'!$C$2:$C$1060=A3);--('Oracle viðföng'!$E$2:$E$1060=C3);'Oracle
viðföng'!$J$2:$J$1060)<0;SUMPRODUCT(--('Oracle
viðföng'!$C$2:$C$1060=A3);--('Oracle viðföng'!$E$2:$E$1060=C3);'Oracle
viðföng'!$J$2:$J$1060)0);#REF!;"1"))
But I always get #REF
Can anybody solve this problem or have another solution?

Thanks Geir

Compare Sheets values in two colums
 

I want to match if all numbers in Sheet2 are in Sheet1

Presume you want to check col A in Sheet2 vs col A in Sheet1,
likewise for col B

In Sheet2,
In C1: =IF(A1="","",--ISNUMBER(MATCH(A1,Sheet1!A:A,0)))
Copy C1 to D1, fill down to the last row of source data in cols A/B

Cols C & D will return the corresponding results:
0 for unmatched numbers
1 for matched numbers
blank: "" for any blank source cells

P/s: Change the commas in the expression to semicolons to suit your version
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Geir" wrote:
I have two sheets and want to match them together.
Example:
Sheet1.
A C
12 555
19 666

Sheet2

A C
12 555
22 333
I want to match if all numbers in Sheet2 are in Sheet1.
I am using =IF((SUMPRODUCT(--('Oracle viðföng'!$C$2:$C$1060=A3);--('Oracle
viðföng'!$E$2:$E$1060=C3);'Oracle
viðföng'!$J$2:$J$1060)=0);IF(OR(SUMPRODUCT(--('Oracle
viðföng'!$C$2:$C$1060=A3);--('Oracle viðföng'!$E$2:$E$1060=C3);'Oracle
viðföng'!$J$2:$J$1060)<0;SUMPRODUCT(--('Oracle
viðföng'!$C$2:$C$1060=A3);--('Oracle viðföng'!$E$2:$E$1060=C3);'Oracle
viðföng'!$J$2:$J$1060)0);#REF!;"1"))
But I always get #REF
Can anybody solve this problem or have another solution?

Thanks Geir