for Excel version 2007 or newer you can use:

=IFERROR(VLOOKUP(H17,L2:Q75,J2,0),"Game?")

Regards

Claus B.


Sub Test works, but Sub TestX does not. TextX errors out on word "VLookup" for both lines.

Howard

Sub test()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim rngLook As Range: Set rngLook = ws.Range("E1:F5")
Dim currName As String
Dim cellNum As Variant

currName = Range("B1")
cellNum = Application.VLookup(currName, rngLook, 2, False)
If IsError(cellNum) Then
MsgBox "Game?"
Else
MsgBox cellNum
End If
End Sub


Sub testX()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim rngLook As Range: Set rngLook = ws.Range("E1:F5")
Dim currName As String

currName = Range("B1")
'IFERROR(VLOOKUP(H17,L2:Q75,J2,0),"Game?")

'MsgBox = Application.IfError(VLookup(currName, rngLook, 2, False), "Game?")
MsgBox = IfError(VLookup(currName, rngLook, 2, False), "Game?")
End Sub