HOW TO MATCH A CELLS FROM TWO DIFFERENT SHEETS IN ONE SHEET

[AYSHANA]

Hello!
good day!

im asking for a help regarding my formula.

so i have 2 different sheets and trying to combine them in one sheet.

i want my formula to check if my (ACC no in the weekly sheet) is equal to (ACC no in the daily sheet), if they are equal to be reflect in the (database sheet) and if not to skip and search for another (ACC no which are equal).

i have try to use the index match or IF condition with VLOOKUP but unfortunately it didn't work with me.

=IFERROR(INDEX(WEEKLY!$C64:$C$4000,MATCH(WEEKLY!$C64,DAILY!$D$6:$D$4000,0)),""

in the database sheet as u can see i got blank cells infact there are some ACC no which are equal but it didnt reflect.

kindly refer to the attachment

thank you.

 

[AYSHANA]

Anyone can help

 

[alansidman]

Cannot manipulate data in a picture. Please reload your data using XL2BB.

 

[AYSHANA]

DAILY

REPAIR.xlsx
	C	D	E	F		H	I	J	K
4	Orders	Acc Number	CID	PT Number		CRA Receive Date Time	Workbench Date Time	Result Date Time	Workbench received to Result
12	BGRPS	T778742				14-02-23 10:32	14-02-23 10:42	14-02-23 12:07	110
13	BGRPS	T771917				14-02-23 2:34	14-02-23 2:39	14-02-23 3:17	38
14	BGRPS	T783471				14-02-23 12:39	14-02-23 12:46	14-02-23 13:16	30
15	BGRPS	T788771				14-02-23 16:36	14-02-23 17:15	14-02-23 17:54	39
16	BGRPS	T792339				14-02-23 20:44	14-02-23 20:45	14-02-23 21:23	39
17	BGRPS	T772275				14-02-23 5:19	14-02-23 5:21	14-02-23 6:03	42
18	BGRPS	T784801				14-02-23 14:18	14-02-23 15:00	14-02-23 16:04	64
19	BGRPS	T785634				14-02-23 14:15	14-02-23 15:00	14-02-23 15:34	34
31	BGRPS	W481444				15-02-23 14:17	15-02-23 14:25	15-02-23 14:59	34
32	BGRPS	W488807				15-02-23 22:16	15-02-23 22:24	15-02-23 22:56	32
33	BGRPS	W476272				15-02-23 11:36	15-02-23 11:19	15-02-23 11:51	32
34	BGRPS	W467886				15-02-23 7:46	15-02-23 8:26	15-02-23 9:00	34
35	BGRPS	W471257				15-02-23 9:16	15-02-23 9:27	15-02-23 10:07	40
36	BGRPS	W486313				15-02-23 20:47	15-02-23 21:08	15-02-23 21:57	49
37	BGRPS	W475023				15-02-23 11:35	15-02-23 12:12	15-02-23 12:50	38
38	BGRPS	W482268				15-02-23 14:21	15-02-23 14:25	15-02-23 14:59	34
39	BGRPS	W487068				15-02-23 18:59	15-02-23 19:00	15-02-23 21:06	126
40	BGRPS	W488206				15-02-23 21:04	15-02-23 21:08	15-02-23 21:38	30
41	BGRPS	W483517				15-02-23 16:32	15-02-23 16:58	15-02-23 17:38	40
42	BGRPS	W481973				15-02-23 14:34	15-02-23 14:47	15-02-23 15:20	33
43	BGRPS	W485365				15-02-23 18:07	15-02-23 18:45	15-02-23 21:05	140
44	BGRPS	W475711				15-02-23 11:38	15-02-23 12:25	15-02-23 13:00	35
45	BGRPS	W479787				15-02-23 12:40	15-02-23 12:45	15-02-23 13:14	29
46	BGRPS	W488587				15-02-23 21:16	15-02-23 21:17	15-02-23 21:50	33
47	BGRPS	H436345				16-02-23 18:17	16-02-23 18:22	16-02-23 19:05	43
48	BGRPS	H431612				16-02-23 14:18	16-02-23 14:25	16-02-23 14:56	31
49	BGRPS	H418418				16-02-23 3:57	16-02-23 4:01	16-02-23 4:34	33
50	BGRPS	H438164				16-02-23 19:50	16-02-23 19:53	16-02-23 20:38	45
51	BGRPS	H437124				16-02-23 18:37	16-02-23 18:41	16-02-23 19:22	41
52	BGRPS	H433444				16-02-23 15:58	16-02-23 16:32	16-02-23 17:31	59
53	BGRPS	H439472				16-02-23 22:50	16-02-23 22:53	16-02-23 23:29	36
54	BGRPS	W489046				15-02-23 23:29	15-02-23 23:44	16-02-23 0:15	31
55	BGRPS	H437461				16-02-23 19:11	16-02-23 19:24	16-02-23 19:57	33
56	BGRPS	H436195				16-02-23 17:39	16-02-23 17:45	16-02-23 18:28	43
57	BGRPS	H419679				16-02-23 7:33	16-02-23 7:39	16-02-23 8:19	40
58	BGRPS	F390799				17-02-23 10:06	17-02-23 10:23	17-02-23 11:05	42
59	BGRPS	F391568				17-02-23 10:27	17-02-23 11:13	17-02-23 11:46	33
60	BGRPS	F386689				17-02-23 8:12	17-02-23 8:16	17-02-23 8:52	36
61	BGRPS	F400614				17-02-23 21:20	17-02-23 21:22	17-02-23 21:57	35
62	BGRPS	F397539				17-02-23 17:19	17-02-23 17:21	17-02-23 18:02	41
63	BGRPS	F400860				17-02-23 22:52	17-02-23 22:55	17-02-23 23:38	43
64	BGRPS	F397838				17-02-23 17:38	17-02-23 17:42	17-02-23 18:17	35
65	BGRPS	F395257				17-02-23 12:51	17-02-23 12:53	17-02-23 13:25	32
66	BGRPS	F384902				17-02-23 6:23	17-02-23 6:43	17-02-23 7:23	40
67	BGRPS	F388637				17-02-23 11:10	17-02-23 11:24	17-02-23 11:55	31
68	BGRPS	F395461				17-02-23 14:36	17-02-23 14:54	17-02-23 15:53	59
69	BGRPS	F395736				17-02-23 15:36	17-02-23 15:41	17-02-23 16:30	49
70	BGRPS	F400067				17-02-23 20:10	17-02-23 20:13	17-02-23 20:51	38
71	BGRPS	F400754				17-02-23 22:00	17-02-23 22:05	17-02-23 23:00	55
72	BGRPS	S460785				18-02-23 20:56	18-02-23 20:59	18-02-23 21:33	34
73	BGRPS	S456757				18-02-23 10:25	18-02-23 10:33	18-02-23 11:11	38
74	BGRPS	S459376				18-02-23 14:43	18-02-23 14:44	18-02-23 15:17	33
75	BGRPS	S459068				18-02-23 14:01	18-02-23 14:02	18-02-23 14:35	33
76	BGRPS	S460252				18-02-23 18:20	18-02-23 18:30	18-02-23 19:07	37
77	BGRPS	S461054				19-02-23 22:35	18-02-23 22:55	18-02-23 23:28	33
78	BGRPS	S460113				18-02-23 17:31	18-02-23 17:35	18-02-23 18:08	33
DAILY

 

[AYSHANA]

WEEKLY

REPAIR.xlsx
	C	D	E	F	G	H	I	J
4	Acc No#	PT Number	PT Name	UNIT NUMBER Allocated in BGRPS order	BLOOD BGRP	UNIT ALLOCATED IN SYSTEM	UNIT ISSUED IN SYSTEM
58	T788912			U123	O Positive	15-02-23 14:55	15-02-23 16:23
59	W476272			U124	O Positive	16-02-23 14:55	16-02-23 16:23
60	W476272			U125	O Positive	17-02-23 14:55	17-02-23 16:23
61	T787716			U126	O Positive	18-02-23 14:55	18-02-23 16:23
62	T782689			U127	B Positive	19-02-23 14:55	19-02-23 16:23
63	W475023			U128	A Positive	20-02-23 14:55	20-02-23 16:23
64	H439472			U129	B Positive	21-02-23 14:55	21-02-23 16:23
65	S461054			U130	B Positive	22-02-23 14:55	22-02-23 16:23
66	S461054			U131	B Negative	23-02-23 14:55	23-02-23 16:23
67	S461054			U132	B Negative	24-02-23 14:55	24-02-23 16:23
68	S460113			U133	A Positive	25-02-23 14:55	25-02-23 16:23
69	S460113			U134	A Positive	26-02-23 14:55
70	S460113			U135	A Positive	27-02-23 14:55	27-02-23 16:23
71	S460113			U136	A Positive	28-02-23 14:55	28-02-23 16:23
72	F397838			U137	O Positive	01-03-23 14:55	01-03-23 16:23
73	F397838			U138	O Positive	02-03-23 14:55	02-03-23 16:23
74	F397838			U139	O Positive	03-03-23 14:55	03-03-23 16:23
75	F397838			U140	O Positive	04-03-23 14:55	04-03-23 16:23
76	F397838			U141	O Positive	05-03-23 14:55	05-03-23 16:23
77	F397838			U142	O Positive	06-03-23 14:55	06-03-23 16:23
78	F395257			U143	O Positive	07-03-23 14:55	07-03-23 16:23
79	M313562			U144	B Positive	08-03-23 14:55	08-03-23 16:23
80	T788771			U145	A Positive	09-03-23 14:55	09-03-23 16:23
81	M309941			U146	A Positive	10-03-23 14:55
82	T792339			U147	O Positive	11-03-23 14:55	11-03-23 16:23
83	F384902			U148	O Positive	12-03-23 14:55	12-03-23 16:23
84	M319409			U149	A Positive	13-03-23 14:55	13-03-23 16:23
85	M319409			U150	A Positive	14-03-23 14:55	14-03-23 16:23
86	T791422			U151	A Positive	15-03-23 14:55	15-03-23 16:23
87	T791422			U152	A Positive	16-03-23 14:55	16-03-23 16:23
88	M305558			U153	B Positive	17-03-23 14:55	17-03-23 16:23
89	M305558			U154	B Positive	18-03-23 14:55	18-03-23 16:23
90	M328601			U155	AB Positive	19-03-23 14:55	19-03-23 16:23
91	M328601			U156	A Positive	20-03-23 14:55	20-03-23 16:23
92	T772501			U157	B Positive	21-03-23 14:55	21-03-23 16:23
93	T772501			U158	B Positive	22-03-23 14:55	22-03-23 16:23
94	F400754			U159	A Positive	23-03-23 14:55	23-03-23 16:23
95	S459588			U160	A Positive	24-03-23 14:55
96	S459588			U161	A Positive	25-03-23 14:55	25-03-23 16:23
97	S459588			U162	A Positive	26-03-23 14:55
98	S459588			U163	A Positive	27-03-23 14:55	27-03-23 16:23
99	S460938			U164	O Positive	28-03-23 14:55	28-03-23 16:23
100	S460938			U165	O Positive	29-03-23 14:55	29-03-23 16:23
101	X338437			U166	O Positive	30-03-23 14:55	30-03-23 16:23
WEEKLY

 

[AYSHANA]

Cannot manipulate data in a picture. Please reload your data using XL2BB.

DATA BASE

REPAIR.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	ORDER	ACC NUMBER	CID	PT NUMBER	PT NAME	CRA RECEIVE DATE TIME	Workbench Date Time	Result Date Time	Workbench received to Result	Result Tech	UNIT NUMBER Allocated in BGRPS order	BLOOD BGRP	UNIT ALLOCATED IN SYSTEM	UNIT ISSUED IN SYSTEM
59	BGRPS	T791422	0	0	0	19-02-23 22:35	18-02-23 22:55	18-02-23 23:28	33	0	U151	A Positive	15-03-23 14:55	15-03-23 16:23
60	BGRPS	T791422	0	0	0	18-02-23 17:31	18-02-23 17:35	18-02-23 18:08	33	0	U151	A Positive	15-03-23 14:55	15-03-23 16:23
61	BGRPS
62	BGRPS
63	BGRPS	F400754	0	0	0	00-01-00 0:00	00-01-00 0:00	00-01-00 0:00	0	0	U159	A Positive	23-03-23 14:55	23-03-23 16:23
64	BGRPS	0
65	BGRPS	0
66	BGRPS	0
67	BGRPS	0
68	BGRPS	0
69	BGRPS	0
70	BGRPS	0
71	BGRPS	0
72	BGRPS	0
73	BGRPS	0
74	BGRPS	0
75	BGRPS	0
76	BGRPS	0
77	BGRPS	0
78	BGRPS	0
79	BGRPS
80	BGRPS	M305558									U153	B Positive	17-03-23 14:55	17-03-23 16:23
81	BGRPS
82	BGRPS	T772501	0	0	0	00-01-00 0:00	00-01-00 0:00	00-01-00 0:00	0	0	U157	B Positive	21-03-23 14:55	21-03-23 16:23
83	0	0
84	0
85	0
86	0	0
DATABASE

Cell Formulas
Range	Formula
C59:C86	C59	=DAILY!$C60
D59:D86	D59	=IFERROR(INDEX(WEEKLY!$C59:$C$4000,MATCH(WEEKLY!$C59,DAILY!$D$6:$D$4000,0)),"")
E59:E86	E59	=IFERROR(INDEX(DAILY!$E60:$E$200000,MATCH($D$5:$D$184506,DAILY!$D$6:$D$200000,0)),"")
F59:F86	F59	=IFERROR(INDEX(DAILY!$F60:$F$200000,MATCH($D$5:$D$184506,DAILY!$D$6:$D$200000,0)),"")
G59:G86	G59	=IFERROR(INDEX(DAILY!$G60:$G$200000,MATCH($D$5:$D$184506,DAILY!$D$6:$D$200000,0)),"")
H59:H86	H59	=IFERROR(INDEX(DAILY!$H60:$H$200000,MATCH($D$5:$D$184506,DAILY!$D$6:$D$20000,0)),"")
I59:I86	I59	=IFERROR(INDEX(DAILY!$I60:$I$200000,MATCH($D$5:$D$6591,DAILY!$D$6:$D$200000,0)),"")
J59:J86	J59	=IFERROR(INDEX(DAILY!$J60:$J$200000,MATCH($D$5:$D$184506,DAILY!$D$6:$D$200000,0)),"")
K59:K86	K59	=IFERROR(INDEX(DAILY!$K60:$K$200000,MATCH($D$5:$D$184506,DAILY!$D$6:$D$200000,0)),"")
L59:L86	L59	=IFERROR(INDEX(DAILY!$L60:$L$200000,MATCH($D$5:$D$184506,DAILY!$D$6:$D$200000,0)),"")
M59:M86	M59	=IFERROR(INDEX(WEEKLY!$F59:$F200054,MATCH($D$5:$D$184506,WEEKLY!$C59:$C200054,0)),"")
N59:N86	N59	=IFERROR(INDEX(WEEKLY!$G59:$G200054,MATCH($D$5:$D$184506,WEEKLY!$C59:$C200054,0)),"")
O59:O86	O59	=IFERROR(INDEX(WEEKLY!$H59:$H200054,MATCH($D$5:$D$184506,WEEKLY!$C59:$C200054,0)),"")
P59:P86	P59	=IFERROR(INDEX(WEEKLY!$I59:$I200054,MATCH($D$5:$D$184506,WEEKLY!$C59:$C200054,0)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K5:K4000	Cell	contains a blank value	text	YES
K5:K4000	Cell Value	>60	text	YES