How to compare a value in two spreadsheets

[edojan]

Hi

I need to compare the values and see if they match or not. If they match for it to say "MATCH" or if not error or something. They key is the SalesID and it has other columns that need to be matched see below.

Thanks!


Sheet 1:
Sales ID, Prod1, Prod2,Prod 3
1,55,6,7
2,2,6,77
3,0,x,y


Sheet 2:
Sales ID, Prod1, Prod2,Prod 3
1,55,6,7
2,2,6,55
3,b,a,z

Sheet 3: (What it should look like)
Sales ID, Prod1, Prod2,Prod 3
1,match,match,match
2,match,match,error
3,error,error,error

 

[schielrn]

I assume they do not always line up row for row, but if they do this can be a lot simpler than I have made it:

Excel Workbook
	A	B	C	D
1	Sales ID	Prod1	Prod2	Prod3
2	1	55	6	7
3	2	2	6	77
4	3	0	x	y
Sheet1

Excel Workbook
	A	B	C	D
1	Sales ID	Prod1	Prod2	Prod3
2	1	55	6	7
3	2	2	6	55
4	3	b	a	z
Sheet2

Excel Workbook
	A	B	C	D
1	Sales ID	Prod1	Prod2	Prod3
2	1	match	match	match
3	2	match	match	error
4	3	error	error	error
Sheet3

Cell Formulas
Range	Formula
B2	=IF(INDEX(Sheet1!$B$2:$D$4,MATCH($A2,Sheet1!$A$2:$A$4,0),MATCH(B$1,Sheet1!$B$1:$D$1,0))=INDEX(Sheet2!$B$2:$D$4,MATCH($A2,Sheet2!$A$2:$A$4,0),MATCH(B$1,Sheet2!$B$1:$D$1,0)),"match","error")

Hope that helps and post back with any questions.

 

[edojan]

thank you!!!