Here's one way...
Code:
=if(COUNTIFS('Latest SQ01 Dumps'!A:A,'Cost Data Sheet Lookup Table'!A3,'Latest SQ01 Dumps'!O:O,"Open")>0,"Open","Closed")
A bit of creative criticism, I would rename your tabs to something more concise ideally without spaces. This will make your formulas a bit more aesthetic. For example, if your tabs were Validation, Dumps, and Lookups, the formula then would be:
Code:
=if(COUNTIFS(Dumps!A:A,Lookups!A3,Dumps!O:O,"Open")>0,"Open","Closed")