Returning text, date, time, location from one Excel sheet to another

[NBVC]

Try:

=IFERROR(INDEX(ATMS!H$2:H$100,MATCH(1,INDEX((ATMS! $B$2:$B$100=G27)*(ATMS!$O$2:$O$100<>""),0),0 )),"")


Note: This is an array formula, and although it doesn't need to be confirmed as a usual array formula, you will need to limit your ranges to defined sizes instead of full columns...