MATCH() on a 2D array?
 

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

Answer: MATCH() on a 2D array?
 

Yes, it is possible to use the MATCH() function on a 2D array in Excel 2003 to find the position of a particular entry. Here's how you can do it:

1. Select the range of cells that contains the 2D array.
2. Press F2 to enter edit mode.
3. Type the cell reference of the entry you want to find (e.g. "B2") and press Enter.
4. Press Ctrl + Shift + Enter to enter the formula as an array formula. This will enclose the formula in curly braces {}.
5. The result will be a 2-element vector of {row, column} that corresponds to the position of the entry in the array.

Alternatively, you can use the ROW() and COLUMN() functions to convert the 2-element vector into a single number representing the "cell number". Here's how:

1. Use the MATCH() function as described above to get the 2-element vector of {row, column}.
2. Use the ROW() function to get the row number of the entry: ROW(range)+row-1.
3. Use the COLUMN() function to get the column number of the entry: COLUMN(range)+column-1.
4. Use the formula: (row-1)*COLUMNS(range)+column to get the "cell number" of the entry.

MATCH() on a 2D array?
 

Hi,

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

=CELL("Address",INDEX(Table,MATCH(TRUE,COUNTIF(OFF SET(Table,ROW(Table)-CELL("Row",Table),0,1),D1)0,0),MATCH(D1,INDEX(Tab le,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

"Geoff Lambert" wrote:

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

MATCH() on a 2D array?
 

Here's another way...

=CELL("address",INDEX(Table,MIN(IF(Table=D1,ROW(Ta ble)-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!

In article ,
Geoff Lambert wrote:

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

MATCH() on a 2D array?
 

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

"Mike H" wrote in message
...
Hi,

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

=CELL("Address",INDEX(Table,MATCH(TRUE,COUNTIF(OFF SET(Table,ROW(Table)-CELL("Row",Table),0,1),D1)0,0),MATCH(D1,INDEX(Tab le,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

"Geoff Lambert" wrote:

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

MATCH() on a 2D array?
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Geoff Lambert" wrote in message
...
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

MATCH() on a 2D 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?

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

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

=CELL("Address",INDEX(Table,MATCH(TRUE,COUNTIF(OFF SET(Table,ROW(Table)-CELL("Row",Table),0,1),D1)0,0),MATCH(D1,INDEX(Tab le,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

"Geoff Lambert" wrote:

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

MATCH() on a 2D array?
 

For "cell number" try (with array A1:C3 and E2 as lookup):

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

"Geoff Lambert" wrote:

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