Skip to content

Instantly share code, notes, and snippets.

@fralau fralau/excel_lookup.md
Last active Aug 30, 2018

Embed
What would you like to do?
Finally a simple table lookup function in Excel!

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.