Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 150
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 373
Default 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






  #6   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 272
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match & array Carmen Excel Worksheet Functions 2 July 31st 07 04:08 AM
array match Mike S Excel Worksheet Functions 6 March 1st 07 04:04 PM
index match array function-returning only first match, need last. Julie Olsen Excel Worksheet Functions 3 December 29th 06 01:50 AM
Trying to MATCH a value out of a cell on an ARRAY - help pls goofy-duck Excel Worksheet Functions 2 October 10th 06 05:58 AM
Match as well as does not match array function Vikram Dhemare Excel Discussion (Misc queries) 7 April 25th 06 09:15 AM


All times are GMT +1. The time now is 11:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"