Look-up and return a cell value when a specific condition is met

[JAV1]

Hello Excel Pros, is there a way to return a cell value based on a specific condition? Attached is a simple spreadsheet where a formula is sought to return a Complete Date from the first "PM" Problem Type row when the Unit # remains same. Thank you in advance!

Look-up and cell value return when a specific condition is met.xlsx
	A	B	C	D
1	Complete Date	Unit #	Problem Type	Formula needed to have the following Outcome
2	1/10/2018 14:18	400	LG	1/29/2018 16:18
3	1/10/2018 20:12	400	CM	1/29/2018 16:18
4	1/11/2018 13:30	400	CM	1/29/2018 16:18
5	1/17/2018 13:03	400	CM	1/29/2018 16:18
6	1/26/2018 4:56	400	LG	1/29/2018 16:18
7	1/29/2018 16:18	400	PM
8	1/30/2018 0:58	400	CM	4/25/2018 14:36
9	1/31/2018 2:11	400	CM	4/25/2018 14:36
10	2/1/2018 14:04	400	CM	4/25/2018 14:36
11	2/1/2018 13:58	400	CM	4/25/2018 14:36
12	2/1/2018 14:27	400	CM	4/25/2018 14:36
13	2/7/2018 2:44	400	CM	4/25/2018 14:36
14	3/8/2018 20:24	400	CM	4/25/2018 14:36
15	3/9/2018 9:30	400	CM	4/25/2018 14:36
16	3/9/2018 9:32	400	CM	4/25/2018 14:36
17	4/5/2018 14:38	400	CM	4/25/2018 14:36
18	4/5/2018 14:38	400	CM	4/25/2018 14:36
19	4/5/2018 14:38	400	CM	4/25/2018 14:36
20	4/5/2018 14:38	400	CM	4/25/2018 14:36
21	4/24/2018 16:51	400	CM	4/25/2018 14:36
22	4/25/2018 14:36	400	PM
23	5/1/2018 10:46	402	CM	7/16/2018 0:00
24	5/2/2018 19:54	402	CM	7/16/2018 0:00
25	6/8/2018 21:51	402	CM	7/16/2018 0:00
26	6/10/2018 18:36	402	CM	7/16/2018 0:00
27	7/16/2018 0:00	402	PM
28	7/25/2018 0:00	407	CM
29	7/25/2018 0:00	407	CM
30	7/26/2018 0:00	407	LG
Sheet1

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D
1	Complete Date	Unit #	Problem Type
2	10/01/2018 14:18	400	LG	29/01/2018 16:18
3	10/01/2018 20:12	400	CM	29/01/2018 16:18
4	11/01/2018 13:30	400	CM	29/01/2018 16:18
5	17/01/2018 13:03	400	CM	29/01/2018 16:18
6	26/01/2018 04:56	400	LG	29/01/2018 16:18
7	29/01/2018 16:18	400	PM
8	30/01/2018 00:58	400	CM	25/04/2018 14:36
9	31/01/2018 02:11	400	CM	25/04/2018 14:36
10	01/02/2018 14:04	400	CM	25/04/2018 14:36
11	01/02/2018 13:58	400	CM	25/04/2018 14:36
12	01/02/2018 14:27	400	CM	25/04/2018 14:36
13	07/02/2018 02:44	400	CM	25/04/2018 14:36
14	08/03/2018 20:24	400	CM	25/04/2018 14:36
15	09/03/2018 09:30	400	CM	25/04/2018 14:36
16	09/03/2018 09:32	400	CM	25/04/2018 14:36
17	05/04/2018 14:38	400	CM	25/04/2018 14:36
18	05/04/2018 14:38	400	CM	25/04/2018 14:36
19	05/04/2018 14:38	400	CM	25/04/2018 14:36
20	05/04/2018 14:38	400	CM	25/04/2018 14:36
21	24/04/2018 16:51	400	CM	25/04/2018 14:36
22	25/04/2018 14:36	400	PM
23	01/05/2018 10:46	402	CM	16/07/2018 00:00
24	02/05/2018 19:54	402	CM	16/07/2018 00:00
25	08/06/2018 21:51	402	CM	16/07/2018 00:00
26	10/06/2018 18:36	402	CM	16/07/2018 00:00
27	16/07/2018 00:00	402	PM
28	25/07/2018 00:00	407	CM
29	25/07/2018 00:00	407	CM
30	26/07/2018 00:00	407	LG
31
Primary

Cell Formulas
Range	Formula
D2:D30	D2	=IF(C2="PM","",IFNA(INDEX(A2:A30,MATCH(1,(B2:B30=B2)*(C2:C30="PM"),0)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Joe4]

Welcome to the Board!

Place this formula in cell D2 and copy down for all rows:

=IFERROR(IF(C2<>"PM",INDEX(A2:A$1000,MATCH("PM",C2:C$1000,0)),""),"")

 

[JAV1]

Amazing! Solutions provided by both Excel mavens Joe4 and Fluff worked like a charm. Thank you for the speedy resolution!

 

[Fluff]

Glad we could help & thanks for the feedback.