formula to find text and return its cell address

[adhynagara]

Hello... Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this? Thanx in advance!

 

[Aladin Akyurek]

Hi,

I have a similar problem however I have the same text 'ab' appear multiple times in the column..eg :

a1 ab
a2 cd
a3 ef
a4 gh
a5 ij
a6 kl
a7 ab
a8 ab
a9 mn

when i use ADDRESS(MATCH("ab",$A$1:$A$9,0),1) i keep getting the first encountered address which is a1, whereas I'd like to see a1, a7, a8..please help

Thanks,
Abhinav

C1: ab

C2, control+shift+enter, not just enter, and copy down:

=IFERROR(CELL("address",INDEX($A$1:$A$9,SMALL(IF($A$1:$A$9=C$1,
  ROW($A$1:$A$9)-ROW($A$1)+1),ROWS(C$2:C2)))),"")

 

[zoraxe]

=IF($A1="ab",ADDRESS(ROW($A1),1,1),"")