Problem: how to make a simple lookup by key/field name in Excel?
I find it hard to believe that Microsoft has not gone all the way to provide a solution to its customers!
Suppose, that, in your Excel grid, you have a table like this, named
Taking the cue from maxhob17 on StackOverflow, here is a generalized solution that will work with any named table:
Public Function TLookup(tablename As String, key As String, col As String) 'From a table, find the corresponding value for a key (in the first column) 'Object to represent the table Dim lo As ListObject: Set lo = range(tablename).ListObject 'Integers to act as coordinates Dim x As Integer, y As Integer 'Find the column x = lo.HeaderRowRange.Find(col).column - lo.HeaderRowRange.column + 1 'Use the key to find the row y = lo.DataBodyRange.Columns(1).Find(key).Row - lo.HeaderRowRange.Row ' Return the value at the coordinates x,y TLookup = lo.DataBodyRange(y, x).Value End Function
In the VB Window, insert the code above in a module. Remember to save your Excel file in xlsm format.
Then you should be able to insert the following formula in the your table:
=TLookup("Sales", "Johnson", "Amount")
And get the proper result: 8350.
This trick will change my life; and it might change yours. It cannot be simpler, can it?