Thread: Missing Function: TINDEX

My background was first with relational databases, so I always tend to think about thinks in regards to SQL. I was excited about 2007's Table functions, but noticed a major glaring ommission...the ability to get a single cell based on row name and colum name. INDEX does a nice job using ordinals, but if I want to reference a row/column by names, it doesn't help. An xls with a bunch of INDEX(RangeName, 48,3) makes it difficult to audit and understand as well.

Thus, I rolled my own and called it TINDEX. I know from experience that I have NEVER written a function that somebody else hadn't already invented and likely implemented better. So, herewith, I am posting my code for my TINDEX function to see if:
1.) Anybody can think of a way to speed it up and/or improve its fault tolerance
2.) Somebody has a better solution (I am hoping so).

I have not put a tremendous amount of time in developing this, but I have become VERY reliant on in it my Excel models. I don't doubt it is inefficient, and I have no pride of authorship, so feel free to tear it apart.

The Function:
TINDEX(RangeName, RowName, ColumnName)
returns the value of a single cell within RangeName at the coordinates of RowName and ColumnName. Rows and Columns need not be sorted as in V or HLOOKUP functions, but they must be unique.

Public Function TIndex(Tbl As Range, rowName As Variant, colName As Variant) As Variant
Application.Volatile
'Define variables for column and row ordinal values
Dim colIdx&, rowIdx&

'Loop through all the columns in the range, checking each
'to see if it matches the colName passed by the user
For n = 2 To Tbl.Columns.Count
If Tbl.Cells(1, n).Text = colName Then
colIdx = n
Exit For
End If
Next n

'Loop through all the rows in the range, checking each
'to see if it matches the rowName passed by the user
For n = 1 To Tbl.Rows.Count
If Tbl.Cells(n, 1).Text = rowName Then
rowIdx = n
Exit For
End If
Next n

'Steal the active format of the cell that is found and apply it to the calling cell
ActiveCell.NumberFormat = Tbl.Cells(rowIdx, colIdx).NumberFormat

'Return the value
TIndex = Tbl.Cells(rowIdx, colIdx).Value
End Function

Hi SterlingIV

Welcome to the Forum

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Cheers