Re: How to return cell reference
- From: Ron Coderre <ronREMOVETHIScoderre@xxxxxxxxxxx>
- Date: Sat, 10 Jun 2006 14:41:01 -0700
Well.....these array formulas seem to work fine (so far) with duplicate
values, but they ain't too pretty:
First instance of "John" in A1:M10
Q1:
=ADDRESS(MIN(IF(A1:M10="john",ROW(A1:M10))),MIN(IF(OFFSET(A1:M10,MIN(IF(A1:M10="john",ROW(A1:M10)))-1,,1)="John",COLUMN(OFFSET(A1:M10,MIN(IF(A1:M10="john",ROW(A1:M10)))-1,,1)))))
Last instance of "John" in A1:M10
Q2:
=ADDRESS(MAX(IF(A1:M10="john",ROW(A1:M10))),MAX(IF(OFFSET(A1:M10,MAX(IF(A1:M10="john",ROW(A1:M10)))-1,,1)="John",COLUMN(OFFSET(A1:M10,MAX(IF(A1:M10="john",ROW(A1:M10)))-1,,1)))))
In both formulas, after the ROW reference is established, the COL ref
formulas are restricted to that one row.
***********
Regards,
Ron
XL2002, WinXP
"Ron Coderre" wrote:
Hmmm....It seems the formulas are at the mercy of the way Excel iterates.
through the ranges. If the dupes are in the same column or staggered to the
right and/or down, the correct values are returned. Otherwise, wrong returns:
With Dupe Cells: C3 and A6
WRONG returned value of 1st instance: A3
WRONG returned value of last instance: C6
With Dupe Cells: C3 and F5
Correct returned value of 1st instance: C3
Correct returned value of last instance: F5
***********
Regards,
Ron
XL2002, WinXP
"Dave Peterson" wrote:
I put John in A3 and B1
and it returned A1 as the address.
This formula works for items that aren't duplicated.
(I don't have a solution if the data contains duplicates.)
Peo Sjoblom wrote:
Another way albeit array entered
=ADDRESS(MAX((A1:M10="john")*(ROW(A1:M10))),MAX((A1:M10="john")*(COLUMN(A1:M10))))
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
"Ron Coderre" <ronREMOVETHIScoderre@xxxxxxxxxxx> wrote in message
news:B33E7463-FB3D-42CD-A63B-B24D73DB9BF5@xxxxxxxxxxxxxxxx
See if one of these approaches helps.
If the Name will only be in Col_C:
=CELL("address",INDEX(C:C,MATCH("John",C:C,0)))
If the Name will be in A1:M10, but only ONCE:
=CELL("address",INDEX($A$1:$M$10,SUMPRODUCT(($A$1:$M$10="John")*ROW($A$1:$M$10)),SUMPRODUCT(($A$1:$M$10="John")*COLUMN($A$1:$M$10))))
If you want the 1st instance of the Name in A1:M10 (ARRAY FORMULA*):
=CELL("address",INDEX($A$1:$M$10,MIN(IF($A$1:$M$10="John",ROW($A$1:$M$10))),MIN(IF($A$1:$M$10="John",COLUMN($A$1:$M$10)))))
*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"et" wrote:
Hi all,
I have a worksheet that contains some names in it, I can find them by
using
Vlookup, but I would like to know when they are (cell reference) too, is
there any formula can help me to locate their cells ?
Anyone can help ? Thanks.
et
--
Dave Peterson
- References:
- How to return cell reference
- From: et
- Re: How to return cell reference
- From: Peo Sjoblom
- Re: How to return cell reference
- From: Dave Peterson
- Re: How to return cell reference
- From: Ron Coderre
- How to return cell reference
- Prev by Date: Re: How to return cell reference
- Next by Date: Re: List question
- Previous by thread: Re: How to return cell reference
- Next by thread: Re: Using "Enter" key to tab over
- Index(es):
Relevant Pages
|
