Find value in a range of cells and return values of adjacent cells

[kenrogers]

Hey Everyone - This seems like a pretty straight forward routine but I cannot seem to get the correct formula to get this to work.

Let's say in this table are (4) employee entrances to a building, A, B, C, D. As an employee comes through, their ID, the entrance ID and their time is logged into these groups of three columns per entrance. For example columns A-C are the employee ID, entrance ID and time for entrance A. Then D-F are B and so on. What I would like is in columns O-P to find the employee ID from column N in the data in columns A, D, G, J, and return the corresponding entrance ID and time. The employee ID should only appear one time but if it happens to appear more than once then it should produce an error. There may be times with no employee ID as there are occasional extra door openings with no associated ID. 

Would love any ideas/thoughts/suggestions?

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Input A ID

Input A Source

Input A Time

Input B ID

Input B Source

Input B Time

Input C ID

Input C Source

Input C Time

Input D ID

Input D Source

Input D Time

ID

Source

Time

1

C1

6:11:27.261

C2

6:11:28.018

7

C3

6:11:47.541

8

C0

6:27:49.513

1

C1

6:11:27.591

0

C2

6:11:47.894

0

C3

6:11:49.799

C0

6:27:49.620

2

2

C1

6:11:27.603

4

C2

6:11:49.960

0

C3

6:11:52.700

10

C0

6:28:06.949

3

3

C1

6:11:28.268

9

6:11:49.960

6

C3

6:11:52.886

15

C0

6:28:14.303

4

C1

6:11:46.992

5

C1

6:11:49.453

6

5

C1

6:11:52.279

7

C1

6:11:53.439

8

C1

6:29:07.060

9

10

11

12

13

14

15

16

17

18

19

20

[#2]

@kenrogers Perhaps with PowerQuery as demonstrated in the attached file.

[#3]

@Riny_van_Eekelen Thanks for this however I have never used Power Query. I am actually more familiar with VBA and would look at a solution with it as well. I will try and take some time and do some Power Query tutorials so I understand it better.