Display a Value Based on Time Values and Empty Cells

[indierock413]

EMPLOYEE	STAFF #	AREA	JOB ID	TIME IN	TIME OUT
ADAM	10874	B	5263
TYLER	10865	A	9588	15:47
PAUL	10899	D	8473	15:50	18:59
JOHN	10847	A	9481	15:41	18:42
BRIAN	10838	C	7734	15:53	18:41
RYAN	10833	D	9834	15:37
KYLE	10820	B	4551	14:54	18:54
SHAWN	10811	C	5267
TERRY	10802	A	5906	12:20	15:50

<tbody>
</tbody>

I'm looking to return the Staff # of whichever employee is in Area D and who is currently clocked in ("TIME IN" is populated but "TIME OUT" is empty).

I've been trying to use a VLOOKUP function, but can't seem to figure it out. Am I in the right ballpark, or is there a more efficient function to use?

 

[WarPigl3t]

So if all the criteria you mentioned is met, where should the output go? Also do you need all of the staff numbers that match the criteria or just one of them. Do you want a formula or vba

 

[indierock413]

https://www.dropbox.com/s/vmkq3zm17b4jq6h/Staffing%20Screenshot.png?dl=0

Here is the abridged version of the data table (top) and display table (bottom), with the display table filled in according to how I'm looking to do so. 'TIME IN' and 'TIME OUT' will be manually time-stamped accordingly in the data table as the employees punch in and out.

In the display table, the 'AREA' column (A16-A19) will be constant, while 'EMPLOYEE' (B16-B19), 'EMP. ID' (C16-C19), and 'JOB ID' (D16-D19) will all be set up with formulas (not VBA) to return the appropriate values based on which employee is currently in each area ('TIME IN' value but no 'TIME OUT' value in the data table).

No need to reply with all 9 formulas, as each column's formulas will be the same basic formula but with slightly different criteria. I'm mostly looking for a formula that will display a value (i.e. 'EMPLOYEE') based on the corresponding (1) 'TIME IN' cell being populated, and (2) the 'TIME OUT' cell being an empty cell. The formulas should only return the employee that is currently punched in and working in the designated area, and does not need to include any employees who worked in the same area but have already punched out.

Hopefully this answers your questions.