Finding Mismatches Cont.

[BijanBorazjani]

If the unit number is in both Data Sets, then secondary data needs to be checked. How would I match data from Set 2 to Set 1 and check the next columns for matching data as well. If there would be a mismatch, i need it populate the unit number, and both statuses in their respective columns.

OKC Inventory Equipment 10-22-21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Branch Unit Number	Branch Unit Status	Branch Location	Infor Unit Number	Infor Unit Status	Infor Location					Unit Number Not In Infor	Branch Unit Status	Branch Location		Unit Number Not in Branch	Infor Unit Status	Infor Location		Unit Status Mismatch	Unit Number	Unit Location	Infor Status	Branch Status
2	4050-0094	Ready	MARENT	2200-0275	Ready	MARENT					6666666	Blown Up	MARENT		T53NCF17	On Rent	MARENT
3	4050-1169	Ready	MARENT	2200-0304	Ready	MARENT									1111111
4	4050-1189	Ready	MARENT	2200-0354	Ready	MARENT
5	4050-1256	Ready	MARENT	2210-0036	Ready	MARENT
6	4020-0025	Ready	MARENT	2210-0086	HD1	MARENT
7	S03-092K	Ready	MARENT	2220-0110	Ready	MARENT
8	S03-098K	Ready	MARENT	2220-0146	Ready	MARENT
9	S03-123K	Ready	MARENT	2220-0179	Ready	MARENT
10	S03-166K	Ready	MARENT	2220-0197	Ready	MARENT
11	S03-215K	Ready	MARENT	2220-0209	Ready	MARENT
12	S03-275K	Ready	MARENT	2220-0218	Ready	MARENT
13	S2.5-041	Ready	MARENT	2220-0304	Ready	MARENT
14	2200-0275	Ready	MARENT	2220-0348	Ready	MARENT
15	2200-0304	Ready	MARENT	2220-0367	Ready	MARENT
16	2200-0354	Ready	MARENT	2220-0412	Ready	MARENT
17	R13035	Check In	MARENT	2220-0417	Check In	MARENT
18	R13037	Reserved	MARENT	2220-0436	Reserved	MARENT
19	R13045	Reserved	MARENT	2220-0444	Reserved	MARENT
20	R13048	Check In	MARENT	2220-0446	Check In	MARENT
21	R13074	Check In	MARENT	2220-0987	Check In	MARENT
22	R13077	Reserved	MARENT	2235-0078	Reserved	MARENT
23	R13078	Reserved	MARENT	2235-0083	Reserved	MARENT
24	R13081	Reserved	MARENT	2235-0084	Reserved	MARENT
25	R13094	Reserved	MARENT	2235-0085	Reserved	MARENT
26	R14496	Ready	MARENT	2235-0087	Ready	MARENT
27	R14497	Ready	MARENT	2235-0089	Ready	MARENT
28	R14499	Ready	MARENT	2235-0091	Ready	MARENT
29	R14502	Check In	MARENT	2235-0095	Check In	MARENT
30	R14506	Check In	MARENT	2235-0096	Check In	MARENT
31	R14507	Reserved	MARENT	2235-0098	Reserved	MARENT
32	R14508	Reserved	MARENT	2235-0099	Reserved	MARENT
33	R14510	Reserved	MARENT	2235-0101	Reserved	MARENT
34	R14511	Reserved	MARENT	3000-0011	Reserved	MARENT
35	R14512	Reserved	MARENT	3020-0004	Reserved	MARENT
36	R14513	Reserved	MARENT	3050-0010	Reserved	MARENT
37	R14514	Ready	MARENT	3050-0021	Ready	MARENT
38	R14515	Reserved	MARENT	3050-0033	Reserved	MARENT
39	2235-0078	Check In	MARENT	4020-0025	Check In	MARENT
40	2235-0083	Ready	MARENT	4050-0094	Ready	MARENT
Inventory Comparison

Cell Formulas
Range	Formula
A2:A40	A2	=IFERROR(CELL("contents",Query1[@[Unit Number]]),"")
B2:B40	B2	=IFNA(VLOOKUP(A2,'Current Inventory'!$A$2:$C$1001,2,FALSE),"")
C2:C40	C2	=IFNA(VLOOKUP(A2,'Current Inventory'!$A$2:$C$1001,3,FALSE),"")
D2:D40	D2	=IF('Infor Import'!A2=0,"",(CELL("contents",'Infor Import'!A2)))
E2:E40	E2	=IFNA(VLOOKUP(A2,'Infor Import'!$A$2:$C$1001,2,FALSE),"")
F2:F40	F2	=IFNA(VLOOKUP(A2,'Infor Import'!$A$2:$C$1001,3,FALSE),"")
K2	K2	=IFERROR(FILTER(A2:A1001,(A2:A1001<>"")*(COUNTIF(D2:D1001,A2:A1001)=0)),"")
L2	L2	=IFNA(VLOOKUP(K2,$A$2:$C$1001,2,FALSE),"")
M2	M2	=IFNA(VLOOKUP(K2,$A$2:$C$1001,3,FALSE),"")
O2:O3	O2	=FILTER(D2:D1001,(D2:D1001<>"")*(COUNTIF(A2:A1001,D2:D1001)=0))
P2:P40	P2	=IFNA(VLOOKUP(O2,$D$2:$F$1001,2,FALSE),"")
Q2:Q40	Q2	=IFNA(VLOOKUP(O2,$D$2:$F$1001,3,FALSE),"")
Dynamic array formulas.