MATCH() on a 2D array?

  • Thread starter Thread starter Geoff Lambert
  • Start date Start date
G

Geoff Lambert

In Excel2003 is it possible to use something MATCH() on a 2D array, to
find the position of a particular entry? The result could be expressed
as a 2-element vector of {row,column} or as a single number
representing the "cell number" (e.g. in a 3x3 array, the middle cell
would be #5

Geoff Lambert
 
Hi,

Try this as an array so enter with Ctrl+Shift+Enter

=CELL("Address",INDEX(Table,MATCH(TRUE,COUNTIF(OFFSET(Table,ROW(Table)-CELL("Row",Table),0,1),D1)>0,0),MATCH(D1,INDEX(Table,MATCH(TRUE,COUNTIF(OFFSET(Table,ROW(Table)-CELL("Row",Table),0,1),D1)>0,0),0),0)))

Where
Table is the named range that holds you array
D1 is the value you are looking for.

There has to be a simpler way but i don't know it.

Mike
 
Here's another way...

=CELL("address",INDEX(Table,MIN(IF(Table=D1,ROW(Table)-MIN(ROW(Table))+1)
),MATCH(D1,INDEX(Table,MIN(IF(Table=D1,ROW(Table)-MIN(ROW(Table))+1)),0),
0)))

....where D1 contains the lookup value. Note that the formula needs to
be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Now, this should be doable in VBA with a 2D array of, say integers. Say I
want to know where the number 15 is located. Seems like the problem is it
would want to return 2 numbers corresponding to the 2 dimensions of the
array. Beyond me. Any interest in this? James
 
This seems to work as well ..

If A1:C3 is the 3 x 3 array & D1 houses the lookup value*,
then in say, E1:
=SUMPRODUCT((A1:C3=D1)*{1,2,3;4,5,6;7,8,9})
returns the "cell number" position
*assumed unique within the 3 x 3 array
 
Assumes there is a single instance of the lookup value.

Array entered:

=ADDRESS(MAX((table=D1)*ROW(table)),MAX((table=D1)*COLUMN(table)),4)

If there are multiple instances of the lookup value then you have to define
which instance you want based on direction. For example:

10...20...30
15...30...18
17...22...42

If the lookup value was 30 which one occurs first?
 
For "cell number" try (with array A1:C3 and E2 as lookup):

=MATCH(2,1/FREQUENCY(E2,A1:C3*1))
 
Back
Top