New Contributor

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

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 Replies

# Re: Find value in a range of cells and return values of adjacent cells

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

# Re: Find value in a range of cells and return values of adjacent cells

@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.