I don't like using worksheet function in VBA unless it makes sense. I prefer
the VBA find instead of VLOOK. This solution will work with any table on any
sheet in the workbook. You need to extract the sheet name from the "Named
Range" to prevent errors.

Sub test()

TableName = TOTAL_POPULATION

'you need to get the sheetname from the name range
sheetname = ActiveWorkbook.Names(TableName ).RefersTo
'remove equal sign
sheetname = Mid(sheetname, 2)
'extract sheetname from cell address
sheetname = Left(sheetname, InStr(sheetname, "!") - 1)

MyYear = 2000
CityState = "Cherokee, KS"
With Sheets(sheetname).Range(TableName )
'get year column in row 1 of table
Set YearCell = .Rows(1).Find(what:=MyYear, _
LookIn:=xlValues, lookat:=xlWhole)
If YearCell Is Nothing Then
MsgBox ("Did not find Year : " & MyYear)
Else
'get city row from column 1 of table
Set CityCell = .Columns(1).Find(what:=CityState, _
LookIn:=xlValues, lookat:=xlWhole)
If CityCell Is Nothing Then
MsgBox ("Did not find City : " & CityState)
Else
MsgBox ("Data for Year and city = " & _
Sheets(sheetname).Cells(CityCell.Row, YearCell.Column))
End If
End If

End With


"Dan" wrote:

Good morning Joel,

Thanks a lot for prompt responce, but somehow I am not able to figure out
how to use your suggestions, if you don't mind, can you give a bit more of
detail? I appreciate it very much.

Dan