How to return cell reference

[et]

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

 

[somethinglikeant]

I would use the MATCH function,

MATCH(lookup_value,lookup_array,match_type)

This gives the relative position of an item in an array.
Therefore if your lookup_array is a whole column it will give you the
row number.
Since you know which column you are looking in you can concatenate the
column letter, eg A with the result of the match function
="A"&MATCH("Cyril",A:A,0)

MATCH may not be perfect for what yu need but the right combination of
the following functions could give you what you need


Lookup and Reference functions
ADDRESS Returns a reference as text to a single cell in a worksheet

AREAS Returns the number of areas in a reference

CHOOSE Chooses a value from a list of values

COLUMN Returns the column number of a reference

COLUMNS Returns the number of columns in a reference

HLOOKUP Looks in the top row of an array and returns the value of the
indicated cell

HYPERLINK Creates a shortcut or jump that opens a document stored on
a network server, an intranet, or the Internet

INDEX Uses an index to choose a value from a reference or array

INDIRECT Returns a reference indicated by a text value

LOOKUP Looks up values in a vector or array

MATCH Looks up values in a reference or array

OFFSET Returns a reference offset from a given reference

ROW Returns the row number of a reference

ROWS Returns the number of rows in a reference

TRANSPOSE Returns the transpose of an array

VLOOKUP Looks in the first column of an array and moves across the
row to return the value of a cell


somethinglikeant

 

[Ron Coderre]

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

 

[Peo Sjoblom]

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

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

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

 

[Ron Coderre]

ADDRESS()!
Darn...I couldn't remember the ADDRESS function...
but, yet I remembered CELL("address", rng) would work...go figure!
(I think the epoxy fumes from fixing my little sailboat are killing my brain
cells)

Your formula picks up the LAST instance of the text....

This array formula picks up the FIRST:
=ADDRESS(MIN(IF($A$1:$M$10="John",ROW($A$1:$M$10))),MIN(IF($A$1:$M$10="John",COLUMN($A$1:$M$10))))

***********
Regards,
Ron

XL2002, WinXP

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

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

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]

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.)

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

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

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

 

[Ron Coderre]

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

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.)

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

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


:

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

 

[Peo Sjoblom]

Actually I prefer using Cell("address", index(etc)), it was just another way
and I should have
mentioned that it would work for "one distinct name occurrences", that way I
would have kept that
Swede from Illinois off my heels <vbg>


--

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

ADDRESS()!
Darn...I couldn't remember the ADDRESS function...
but, yet I remembered CELL("address", rng) would work...go figure!
(I think the epoxy fumes from fixing my little sailboat are killing my
brain
cells)

Your formula picks up the LAST instance of the text....

This array formula picks up the FIRST:
=ADDRESS(MIN(IF($A$1:$M$10="John",ROW($A$1:$M$10))),MIN(IF($A$1:$M$10="John",COLUMN($A$1:$M$10))))

***********
Regards,
Ron

XL2002, WinXP

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

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


:

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

 

[Ron Coderre]

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

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

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.)

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

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


:

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]

Someone else "corrected" my proposed formula a while ago.

I didn't get as far as Ron did, though. (Well, unless throwing your hands up in
frustration is the same as getting a solution!)

Actually I prefer using Cell("address", index(etc)), it was just another way
and I should have
mentioned that it would work for "one distinct name occurrences", that way I
would have kept that
Swede from Illinois off my heels <vbg>

--

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

ADDRESS()!
Darn...I couldn't remember the ADDRESS function...
but, yet I remembered CELL("address", rng) would work...go figure!
(I think the epoxy fumes from fixing my little sailboat are killing my
brain
cells)

Your formula picks up the LAST instance of the text....

This array formula picks up the FIRST:
=ADDRESS(MIN(IF($A$1:$M$10="John",ROW($A$1:$M$10))),MIN(IF($A$1:$M$10="John",COLUMN($A$1:$M$10))))

***********
Regards,
Ron

XL2002, WinXP

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


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


:

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