New Function to replicate @XINDEX (Lotus 123)
Request to replicate the fantastic Lotus 123 @XINDEX function:
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()
I have created a UDF too but its VERY slow on large RANGES !! - so a hard-coded, genuine =XINDEX would be a massive improvement
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
Used to be able to do that, treat the top row of a block as labels and the left column as well. then just:
(if those were a column and row "label") to get the intersection's value. You can still do it but have to define Named Ranges. Ad hoc choices, or choices arising from selecting or "building" the column or row header/label to use are not on the table anymore.
Of course, Named Ranges let you use blocks rather than single cell intersections, but we used to have both!
Without this, implicit intersection is a pale shadow of the past. Sadly, when they first announced the "spill" functions, they also said implicit intersection was going away. Not just be unnecessary but going away. They have not kept at pointing that out so maybe it won't, but if it does or is, you can count on never seeing this, in a function or just as general functionality. Sadly.
Got my vote though!