reference of a cell with a given text

edoardodg

New Member
Joined
Oct 3, 2010
Messages
8
Hi all!...

I'm trying to find a formula (or function) that tells me the cell reference of a cell that has a given text in a given range.

For example...I have a worksheet with a cell C3 with the text "dog"...I want a function like =newfind("dog",A1:Z10) to identify that cell and fill the cell with "C3"


any help is very appreciated..

I thank you all in advance and say hi to all since I'm new to the forum,

Edoardo
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUBSTITUTE(CELL("address",INDEX(A1:Z10,MIN(IF(A1:Z10="dog",ROW(A1:Z10)-ROW(A1)+1)),MATCH("dog",INDEX(A1:Z10,MIN(IF(A1:Z10="dog",ROW(A1:Z10)-ROW(A1)+1)),0),0))),"$","")
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
This has to be entered as a function outside the searched range

=newfind("Dog",A1:Z10)

Code:
Function newfind(x As Variant, r As Range) As String
Dim c As Range
For Each c In r
    If c.Value = x Then
        newfind = c.Address(False, False)
        Exit For
    End If
Next c
End Function
 

edoardodg

New Member
Joined
Oct 3, 2010
Messages
8
Domenic...thanks so much for your superquick reply...I've tried it but unluckily it didn't work...don't know why..

anyway I want to say thanks!!!

Edoardo
 

edoardodg

New Member
Joined
Oct 3, 2010
Messages
8

ADVERTISEMENT

VOG...absolutely brilliant...thanks SOOOOO much... you saved me so much time!!...

A very big Grazie from Italy!!

Edoardo
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
Did you confirm the formula with CONTROL+SHIFT+ENTER, instead of just ENTER? If done correctly, Excel will automatically place curly braces {...} around the formula.
 

edoardodg

New Member
Joined
Oct 3, 2010
Messages
8

ADVERTISEMENT

Yes Domenic...I've done that...but it just returns me a 0...don't know why...

thanks anyways!

Edoardo
 

edoardodg

New Member
Joined
Oct 3, 2010
Messages
8
Hi Domenic...

I use this formula and then press ctrl shift enter to set array formula


=SUBSTITUTE(CELL("address";INDEX(A1:Z10;MIN(IF(A1:Z10="dog";ROW(A1:Z10)-ROW(A1)+1));MATCH("dog";INDEX(A1:Z10;MIN(IF(A1:Z10="dog";ROW(A1:Z10)-ROW(A1)+1));0);0)));"$";"")

Thanks!

Edoardo
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
I've checked and re-tested the formula and it seems to returned the desired result. For example, let's assume that A2:C5 contains the data and E2 contains the value of interest, such as 'G'...

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>I</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>B4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>J</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>K</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>H</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR></TBODY></TABLE>

The following formula returns B4...

=SUBSTITUTE(CELL("address",INDEX(A2:C5,MIN(IF(A2:C5=E2,ROW(A2:C5)-ROW(A2)+1)),MATCH(E2,INDEX(A2:C5,MIN(IF(A2:C5=E2,ROW(A2:C5)-ROW(A2)+1)),0),0))),"$","")

...confirmed with CONTROL+SHIFT+ENTER.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,662
Messages
5,573,479
Members
412,532
Latest member
crazzyapple
Top