assumptions regarding the sheet "test" in closed excel file:
- has an excel table
- top left cell of the table is A1
- row 1 of the contains the "Column Headers"
- no blank cells in row 1 and col 1 of the table
- no other tables

Sub vbax_61051_cond_pull_a_cell_value_from_closed_workbook()
'Requires a reference to Microsoft ActiveX Data Objects x.x Library (in VBE, Tools / References)

    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim ClosedFile As String
    Dim LookUpVal
    
    With Worksheets("Sheet1") 'change Sheet1 to suit
        ClosedFile = .Range("B2").Value
        LookUpVal = .Range("A3").Value
    End With
    
    cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "Data Source=" & ClosedFile & ";" _
        & "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""")
    
    rs.Open "SELECT [ColC Header] FROM [test$] where [ColB Header] = " & LookUpVal & ";", cn, adOpenStatic, adLockReadOnly
    
    Worksheets("Sheet1").Range("K15").CopyFromRecordset rs 'change Sheet1 and K15 to suit
    
    rs.Close
    cn.Close

End Sub