Re: 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" <G.Lambert@xxxxxxxxxxx> wrote in message
news:9b8ae3luqb2a1dpr4ggefsaulubt59p777@xxxxxxxxxx
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

  • RE: User List Box - List from Hidden range - VBA worng
    ... In your for loop, ... range is only 1 column wide this will be a single cell). ... Basic Editor type the word on it's own in lower case on a line and hit enter. ... in the array reference - and an array starts with element 0. ...
    (microsoft.public.excel.programming)
  • Re: Wrong data type returned from user-defined function
    ... If you want it to function as an array function then it needs to be ... Dim strFormula As String ... UnRound = CVErr ... would execute just as if I'd typed it in the cell myself. ...
    (microsoft.public.excel.programming)
  • RE: User List Box - List from Hidden range - VBA worng
    ... In your for loop, ... range is only 1 column wide this will be a single cell). ... Basic Editor type the word on it's own in lower case on a line and hit enter. ... in the array reference - and an array starts with element 0. ...
    (microsoft.public.excel.programming)
  • Re: Help with Totals Please
    ... In Cell AB3, the Total of 0 Matches, the Formula would be ... Re: Help with Totals Please ... Just turn on your macro recorder, ... look at the Help reference "About array formulas and how to enter them". ...
    (microsoft.public.excel.programming)
  • Re: Formula help
    ... Roger Govier ... These are mutually exclusive however for each cell within the range. ... Create an array of Ottawa and Toronto, ...
    (microsoft.public.excel.misc)
Loading