Please help. I have looked online and can't find info for formula

[letterly]

Hello,

So lets call the data below is on Sheet 1. I would like to create a formula next to each serial number and FIND serial number on Sheet 2. Then, I would then like to compare the values left of the serial numbers of both sheets and return a value of Match or No Match on Sheet 1. I don't If that can be done with one formula or multiple formulas in multiple columns on Sheet 1. Please help if you can.

 

[AhoyNC]

How is your data set up on sheet 2?
Does it look like the data in my example?
Are your Serial #'s either text or numbers on both sheets?
What are the ranges of your data on sheet 1 & 2?

 

[letterly]

Maybe this will help. Here is close to real data.

There are about 500 serial numbers on sheet 2. I need a formula in Column F on sheet 1 to look at serial number in E column on sheet 1, find serial number on sheet 2 and then compare all five columns to see if they match. I need to know if data has changed. Sheet 1 is the data pulled from management software and sheet 2 is data that is part of master inventory sheet. I need to make sure they match and if they don't I need to know if so I can change it.

 

[AhoyNC]

This works per your example.

Sheet 1

Book1
	A	B	C	D	E	F
1	j3400-a03	170.54.144.12.4	EX3400-48P	20.4R3-S6.3	ab5820440156	No Match
2	j3400-a101	170.54.192.39	EX3400-48P	20.4R3.8	ab5820440157	No Match
3	j3400-a10	170.54.192.89	EX3400-48P	20.4R3-S3.4	ab5820440158	No Match
4	j3400-a	170.54.192.146	EX3400-48P	20.4R3.8	ab5820440159	Match
5	j3400-a02	170.54.136.97	EX3400-48P	20.4R3-S6.3	ab5820440160	Match
Sheet1

Cell Formulas
Range	Formula
F1:F5	F1	=IF(PRODUCT(--(FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)=A1:D1))=1,"Match","No Match")

Sheet 2

Book1
	A	B	C	D	E
1	j340	170.54.144.124	EX3400-48P	20.4R3-S6.3	ab5820440156
2	j3400-a109	170.54.192.40	EX3400-48P	20.4R3.8	ab5820440157
3	j3400-a108	170.54.192.99	EX3400-48P	20.4R3-S3.4	ab5820440158
4	j3400-a	170.54.192.146	EX3400-48P	20.4R3.8	ab5820440159
5	j3400-a02	170.54.136.97	EX3400-48P	20.4R3-S6.3	ab5820440160
Sheet2

 

[letterly]

Wow. It works. Thank you. I only understand half of it. I understand the IF but not the Product, double dash, and filter. I realized I had the show formulas turned on. That is why it looked like it wasn't doin anything.

 

[AhoyNC]

Here is what it does.
See below.
Step 1- the FILTER function gets the data from Sheet 2 based on the serial #.
Step 2 - Matches the date to sheet 1
Step 3 - The double negative changes the TRUE & FALSE to 0 & 1's.
Step 4 - The Product function multiplies the 1's & 0's

If there is a 0 (FALSE) in the array it returns 0 which is no match.
Hope that helps.

Book1
	A	B	C	D	E	F
1	j3400-a03	170.54.144.12.4	EX3400-48P	20.4R3-S6.3	ab5820440156	No Match
2
3	Step 1	FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)
4		j340	170.54.144.124	EX3400-48P	20.4R3-S6.3
5
6	Step 2	FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)=A1:D1
7		FALSE	FALSE	TRUE	TRUE
8
9	Step 3	--(FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)=A1:D1)
10		0	0	1	1
11
12		PRODUCT(--(FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)=A1:D1))
13	Step 4	0
Sheet1

Cell Formulas
Range	Formula
F1	F1	=IF(PRODUCT(--(FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)=A1:D1))=1,"Match","No Match")
B4:E4	B4	=FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)
B7:E7	B7	=FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)=A1:D1
B10:E10	B10	=--(FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)=A1:D1)
B13	B13	=PRODUCT(--(FILTER(Sheet2!$A$1:$D$5,E1=Sheet2!$E$1:$E$5)=A1:D1))
Dynamic array formulas.