Can you help ?
I would like to create a UDF to replicate the fantastic Lotus 123 @XINDEX function:
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
Grateful for any UDF pointers on @XINDEX replication?
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
VBA Code:
[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?