Finally a simple table lookup function in Excel!

excel_lookup.md

Problem: how to make a simple lookup by key/field name in Excel?

I was puzzled by this question in StackOverflow: In Excel there is NO simple way to lookup a value in a named table, where the first column is the key (see how to name a table).

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 sales:

	A	B	C	D
1	Name	Amount	Country	Quarter
2	Smith	2560	GB	Q1
3	Johnson	8350	US	Q2
4	Patrick	2500	GB	Q3

Solution

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?