Re: 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" <MikeH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8ACE33F2-391F-4CA2-80D5-FF893523CE0D@xxxxxxxxxxxxxxxx
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

"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




.

Relevant Pages

  • MATCH() on a 2D array?
    ... In Excel2003 is it possible to use something MATCHon a 2D array, ... find the position of a particular entry? ... representing the "cell number" (e.g. in a 3x3 array, the middle cell ... Geoff Lambert ...
    (microsoft.public.excel.misc)
  • Re: MATCH() on a 2D array?
    ... Assumes there is a single instance of the lookup value. ... Try this as an array so enter with Ctrl+Shift+Enter ... representing the "cell number" (e.g. in a 3x3 array, the middle cell ...
    (microsoft.public.excel.misc)
  • Re: Slow Macros in Excel 2007
    ... In windows excel VBA you would generally use ADO/DAO recordsets and ... You can also transfer a recordset directly to an array ... The Array function in VBA is oriented towards constants rather than data ... Worksheet using a *SINGLE* VBA instruction. ...
    (microsoft.public.excel.programming)
  • Re: Array Formulas in VBA
    ... I finally figured it out Jim. ... There's a "feature" in VBA that screws up the one particular case I happened to be using as an example. ... When using a Variant as an array, you have to declare the size ... Dim varArray As Variant ...
    (microsoft.public.excel.programming)
  • Retrieving Data: Speed of beating down rows vs retrieving from array?
    ... Got a "What If" area on a worksheet. ... Tom Ogilvy suggested vLookup which I guess is the standard way of ... VBA code doing the same thing? ... the array once when the workbook is opened, ...
    (microsoft.public.excel.programming)