excel - Get the reference of a cell containing a certain text -

excel - Get the reference of a cell containing a certain text -

i have somewhere in worksheet cell containing specific text (for exemple "hello") , want know cell it. want know reference of cell (for exemple "b52").

i tried things index or match formula i'm quite confused how should kind of query.

it great if give tips.

thank's in advance !

try little macro:

sub whereis() dim r range each r in activesheet.usedrange if instr(1, r.text, "hello") > 0 msgbox r.address exit sub end if next r end sub

edit#1

here same approach in user defined function (udf) format:

public function whereis(rin range, sin string) string whereis = "" dim r range each r in rin if instr(1, r.text, sin) > 0 whereis = r.address(0, 0) exit function end if next r end function

and here illustration of utilize in worksheet:

edit#2

it possible lookup value in 2 dimensions without vbasay have info like:

in k1 enter:helloin k2 come in array formula

=min(if($a$1:$h$14=k1,column($a$1:$h$14)-column($a$1)+1))

in k3 come in array formula

=min(if($a$1:$h$14=k1,row($a$1:$h$14)-row($a$1)+1))

in k4 enter:

=address(k3,k2)

k2 gives column, k3 gives row, , k4 gives address of cell ($e$11)

array formulas must entered ctrl + shift + enter rather enter key.

excel text reference cell