Excel's INDEX() is much more powerful than Gnumeric's, I've guessed that this is simply due to lack of time for implementation and so I offer here a patch that much improves the situation This patch alters the behaviour of INDEX() and updates the help to match. * INDEX() now returns a reference when appropriate rather than a simple value which means you can use it with functions like ROW(), COLUMN() and CELL() to give useful results. * INDEX() now returns range references or arrays (as appropriate) for some inputs which were previously poorly defined or error states. This matches Excel much more closely than before e.g. INDEX(A1:C4,2,) returns a reference to A2:C2 which can then be used together with array syntax to fill multiple cells with results. * INDEX() now has a special case matching things of the form INDEX(A1:A6,4) which was previously meaningless in Gnumeric and now returns a reference to A4. This is purely an Excel compatibility change and can be removed if considered unsavoury. I can also prepare an XL compat test sheet for INDEX() if that's helpful Nick.
Attachment:
index.patch
Description: Text document