Custom function to replicate @XINDEX (Lotus 123)

[MT_MANC]

Can you help ?
I would like to create a UDF to replicate the fantastic Lotus 123 @XINDEX function:
@XINDEX(range;column-heading;row-heading;[worksheet-heading]) Returns the contents of a cell located at the intersection specified by column-heading, row-heading, and worksheet-heading.
I currently have to do this "manually" by nesting two =MATCH() inside an =INDEX() but would like to simplify into 1 custom function for simplicity..
See attached screenshot from XINDEX_Equivalent_01a.xlsm

eg see Cell C13: =INDEX($B$2:$I$9,MATCH(C$12,$B$2:$B$9,0),MATCH($B13,$B$2:$I$2,0))

Syntax - I am not entirely sure how to specify the above in VBA syntax;more specifically, how to extract the Top Row of "Range" (ColHeading) and First Col of "Range" (RowHeading) (see all VBA expressions in [] below)
Other - and what negative (unintended) ramifications might occur elsewhere in the spreadsheet from this UDF ? Then there's efficiency - I suspect UDFs are more computationally greedy than predefined functions - any tips to avoid snarling up the .XLSX recalc speed ? NB "Range" = $B$2:$I$9

[I]Dim ColHeading as [long]
Dim RowHeading as [long ]
ColHeading = [Extract Top Row of "Range" ]
RowHeading = [Extract First Col of "Range"]
 function XINDEX(Range, ColHeading,RowHeading)
INDEX(Range,MATCH(ColHeading,[$B$2:$B$9],0),MATCH(RowHeading,[$B$2:$I$2],0))
 End Function[/I]

Grateful for any UDF pointers on @XINDEX replication?

 

[Fluff]

How about

Function Xindex(Rng As Range, ColVal As Variant, RowVal As Variant) As Variant
   Dim FndCol As Range, FndRow As Range
   Set FndCol = Rng.Columns(1).Find(ColVal, , , xlWhole, , , False, , False)
   Set FndRow = Rng.Rows(1).Find(RowVal, , , xlWhole, , , False, , False)
   Xindex = Cells(FndCol.Row, FndRow.Column).Value
End Function

Used like
=Xindex($B$2:$J$9,C$12,$B13)
But I'd stick with the regular formula as it will be faster.

 

[MT_MANC]

Thank you - works fine in small ranges, but yes, as I expanded the Lookup range it really slowed down !
(I use @Index a lot in *.123 in some complex/long formula & adding in the extra MATCH code to work in Excel is a REAL pain/complication) How to get Excel to hard-code XINDEX function into next Excel release ?
Does MS respond to user Function requests ? Excel for Windows (Desktop Application): Hot (5433 ideas) – Welcome to Excel’s Suggestion Box!

 

[Fluff]

They do respond, but unless a suggestion gets enough votes, it probably wont be acted on.