Custom function to replicate @XINDEX (Lotus 123)

MT_MANC

New Member
Joined
Sep 2, 2009
Messages
5
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
SheetShot1.jpg
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
How about
VBA Code:
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.
 
Upvote 0
Upvote 0
They do respond, but unless a suggestion gets enough votes, it probably wont be acted on.
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top