How to enter an array, consisting of different arrays, as an input parameter for the "Match()" function?

Question

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?

Answer 1

The short answer is "yes", with Office 365 you can use VSTACK to "treat as one array". Before we get to that, though, let's clear up a few things...

First, your use of OFFSET with INDEX and MATCH is unnecessary. The array parameter in the INDEX function should be treated as the return_array, which can be different than the lookup_array used in the MATCH function.

Second, the optional [match_type] parameter in the MATCH function should be set to 0 (exact match) to ensure the first match is returned. If omitted, the default setting is 1 (Less than), which may appear to work at first glance, but can produce unexpected results if the lookup_array is not sorted in ascending order, or if multiple matches are present.

As such, the following formula would be more appropriate for your first "simple example" shown above:

=INDEX(A2:A3, MATCH(MAX(B2:B3), B2:B3, 0))

Third, the MATCH function will only accept a one-dimensional array (either vertical or horizontal), whereas the INDEX function is perfectly capable of accepting two-dimensional arrays. Furthermore, INDEX will also accept range references consisting of multiple areas, when utilizing the secondary syntax:

=INDEX(reference, row_num, [column_num], [area_num])

To use a range reference with multiple areas, it must be enclosed in brackets. For example:

=INDEX((A2:A3, D2:D3), row_num, [column_num], [area_num])

The optional [area_num] parameter determines which area is returned from the reference. In the above example, 1 would return the corresponding value from A2:A3 and 2 would return the corresponding value from D2:D3.

Lastly, although the MATCH function will only accept a one-dimensional array, it's perfectly capable of handling multiple criteria across multiple columns. The generic formula structure for this is as follows:

// using multiple AND conditions:
=MATCH(1, (range1=value1)*(range2=value2), 0)

// using multiple OR conditions:
=MATCH(1, --(((range1=value1)+(range2=value2))>0), 0)

With these concepts in mind, you can definitely achieve something close to the desired outcome strictly by using combinations of INDEX and MATCH.

Sample Data:

Id	Value	Rubbish	Id	Value
a	1	x	d	2
b	5	y	e	7
c	7	z	f	3

For simplicity, we'll break the process down into steps:

(1) In cell B7, return the maximum of the two "Value" columns shown in the sample data above:

=MAX(B2:B4, E2:E4)

(2) In cell B8, return the row number of the first match found in either the first "Value" column OR the second "Value" column where the value is equal to the maximum value:

=MATCH(1, --(((B2:B4=B7)+(E2:E4=B7))>0), 0)

(3) In cell B9, return the area number where the first match was found:

=MATCH(B7, INDEX((B2:B4, E2:E4), B8, , {1,2}), 0)

(4) In cell B10, return the corresponding value from the "Id" column:

=INDEX((A2:A4, D2:D4), B8, , B9)

Results:

Note: with older versions of Excel, be sure to use Ctrl+Shift+Enter in steps 2 to 4 above.

With Office 365, this method can also be simplified with the LET function:

=LET(
maxVal, MAX(B2:B4, E2:E4),
rowNum, MATCH(1, --(((B2:B4=maxVal)+(E2:E4=maxVal))>0), 0),
areaNum, MATCH(maxVal, INDEX((B2:B4, E2:E4), rowNum, , {1,2}), 0),
INDEX((A2:A4, D2:D4), rowNum, , areaNum))

As stated at the beginning of this answer, though, if you have Office 365, VSTACK can be used as a direct solution to your question:

=INDEX(VSTACK(A2:A4, D2:D4),
   MATCH(MAX(B2:B4, E2:E4), VSTACK(B2:B4, E2:E4), 0))

VSTACK Results:

As you can see, it's entirely possible for these two methods to produce two different results when multiple matches are present; however, BOTH results are correct. The multi-area range reference method returns the first match found (E3=7) when searching across both "Value" columns together, whereas the VSTACK method returns the first match found (B4=7) by first searching the entire first "Value" column, followed by the entire second "Value" column.