4

I was trying to answer another question, when I realised that I don't know how to enter an array, consisting of different arrays, as an input parameter for the INDEX() or the MATCH() function.

Background:
As some of you know, you can find a value somewhere in an Excel sheet, and use the INDEX(MATCH(...)) trick in order to find adjacent cells.

Simple example:

Id Value
a 1
b 5

The "Id", corresponding with the maximal value, can be found as:

=OFFSET(INDEX(B2:B3,MATCH(MAX(B2:B3),B2:B3)),,-1)

This is relatively easy, but when the lookup array is spread over multiple columns, this seems not to work:

Id Value Rubbish Id Value
a 1 x c 2
b 5 x d 7

The lookup array is no longer B2:B3, but B2:B3,E2:E3.
That lookup array can be used for the MAX() function, but when filling in this into the INDEX() and the MATCH() function, Excel seems to think that I'm filling in two input parameters, B2:B3 and E2:E3 (because of the comma in between).

So I need a function, let's call it TREAT_AS_ONE_ARRAY(), so that I can use my formula:

=OFFSET(
   INDEX(
     TREAT_AS_ONE_ARRAY(B2:B3,E2:E3),
     MATCH(MAX(B2:B3,E2:E3),
           TREAT_AS_ONE_ARRAY(B2:B3,E2:E3)
          )
   ),,-1)

Does such a function exist?

JvdV
  • 70,606
  • 8
  • 39
  • 70
Dominique
  • 16,450
  • 15
  • 56
  • 112
  • ??? Why the downvote??? – Dominique Jun 12 '23 at 08:03
  • 2
    I don't think such a syntax exists. But your current sample data would allow for something like `=TOCOL(IF(B2:E3=MAX(B2:E3),A2:D3,NA()),3)`, thus applying all sorts of new dynamic array functions to reshape an array. Ultimately, I don't think this was what you tried to achieve with this question? – JvdV Jun 12 '23 at 08:10
  • 1
    JvdV's set-up is obviously preferable, though theoretically speaking `FREQUENCY(MAX(B2:B3,E2:E3),(B2:B3,E2:E3))` will give you a 1-dimensional array you can then manipulate as befits your needs. – Jos Woolley Jun 12 '23 at 08:16
  • @JvdV: No, I was just looking for a way to use the `INDEX(MATCH())` trick, without actually altering the structure of the data. But as you state, this most probably does not exist. Thanks for your support. – Dominique Jun 12 '23 at 08:34
  • 2
    I don't think that function you mean exists naturally in Excel. You could resolve it with different functions but I guess you are not looking for an answer like that. What you want is your `TREAT_AS_ONE_ARRAY` function – Foxfire And Burns And Burns Jun 12 '23 at 08:40
  • 1
    If only Excel would allow GS syntax, since it's perfectly possible over there. – JvdV Jun 12 '23 at 08:47
  • 2
    do you mean `Index(VSTACK(array1,array2),xmatch(max(array1,array2,vstack(array1,array2)))` ? – P.b Jun 12 '23 at 08:53
  • 1
    Someone downvoted the question. Strange but reason is not given – Mayukh Bhattacharya Jun 12 '23 at 08:54
  • 2
    @MayukhBhattacharya perhaps downvoting should only be available to those with >5000 rep, while upvoting from 0 rep. Of course if downvoting was removed and only upvoting possible then good answers will still rise. – Solar Mike Jun 12 '23 at 10:49
  • 3
    I used the choose() function to control the contents of one range based on selecting between 6 choices - this was to translate between languages. – Solar Mike Jun 12 '23 at 10:51

0 Answers0