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