CODEWITHSUNDEEP
excelexcel-formulams-office
Johan Sjöberg
Johan Sjöberg

Reputation: 49187

Value of cell in named table

How can I get the value of a specific cell in a named table using a readable formula (i.e. a formula where rows/cells are referenced by name rather than index)?

enter image description here

In the above picture I want to display the value of the cell (Income, Feb) of table Financials. What syntax can I use?

Upvotes: 0

Views: 3723

Answers (2)

fralau
fralau

Reputation: 3819

I find it hard to believe that Microsoft has not gone all the way to provide a solution to its customers!

Taking the cue from @maxhob17, 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("Financials", "Feb", "Profit")

This trick will change my life; and it might change yours. It cannot be simpler, can it?

I also documented this solution in github.

Upvotes: 0

maxhob17
maxhob17

Reputation: 612

As I mentioned you could hide the complexity with a user defined function such as

Public Function Financials(month As String, item As String) As String

'Object to represent the table    
Dim lo As ListObject: Set lo = Range("Table1").ListObject

'Integers to act as coordinates    
Dim x As Integer, y As Integer

'Find the column
x = lo.HeaderRowRange.Find(month).Column - lo.HeaderRowRange.Column + 1

'Find the row
y = lo.DataBodyRange.Columns(1).Find(item).Row - lo.HeaderRowRange.Row

' Return the value at the coordinates x,y
Financials = lo.DataBodyRange(y, x).Value

End Function

(Update Range("Table1").ListObject with the name of the table in your workbook, alternatively you could add another parameter to the function)

You would then call this function in a workbook cell such as this example

=Financials("Feb", "Profit")

Upvotes: 1

Related Questions