Re: MATCH() on a 2D array?
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Mon, 10 Sep 2007 10:28:44 -0400
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" <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
.
- References:
- MATCH() on a 2D array?
- From: Geoff Lambert
- MATCH() on a 2D array?
- Prev by Date: Re: I need to know only the last 4 numbers, but these change weekl
- Next by Date: Re: Date calculation
- Previous by thread: Re: MATCH() on a 2D array?
- Next by thread: Re: Execl DrWatson Error 80000007
- Index(es):
Relevant Pages
|
