How to find text in a range of cells?
 

Can someone please put me out of my misery? ...

Suppose each cell in a range contains a single letter,
and the letters are all distinct. Is there a way to
use workbook functions (not a macro) to find the row
& column of the cell that contains a pariticular letter?

How to find text in a range of cells?
 

You could always search with [ctrl]+F and type in what you're looking for.
It'll take you to wherever it is in the document, and you should be able to
see the column and row from there.

"r.e.s." wrote:

Can someone please put me out of my misery? ...

Suppose each cell in a range contains a single letter,
and the letters are all distinct. Is there a way to
use workbook functions (not a macro) to find the row
& column of the cell that contains a pariticular letter?

How to find text in a range of cells?
 

Say your datalist was in A1 to E20.

Enter the value you're looking to find into F1,
and try this *array* formula:

=ADDRESS(MAX((A1:E20=F1)*ROW(A1:E20)),MAX((A1:E20= F1)*COLUMN(A1:E20)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"r.e.s." wrote in message
...
Can someone please put me out of my misery? ...

Suppose each cell in a range contains a single letter,
and the letters are all distinct. Is there a way to
use workbook functions (not a macro) to find the row
& column of the cell that contains a pariticular letter?

How to find text in a range of cells?
 

"Lauren Giles" wrote ...
"r.e.s." wrote:
Can someone please put me out of my misery? ...

Suppose each cell in a range contains a single letter,
and the letters are all distinct. Is there a way to
use workbook functions (not a macro) to find the row
& column of the cell that contains a pariticular letter?

You could always search with [ctrl]+F and type in what you're looking for.
It'll take you to wherever it is in the document, and you should be able
to
see the column and row from there.

Thanks, but by "use workbook functions", I mean use the
functions that can appear in formulas in cells of the workbook.

How to find text in a range of cells?
 

"RagDyer" wrote ...
Say your datalist was in A1 to E20.

Enter the value you're looking to find into F1,
and try this *array* formula:

=ADDRESS(MAX((A1:E20=F1)*ROW(A1:E20)),MAX((A1:E20= F1)*COLUMN(A1:E20)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

Thanks very much ... I'll give it a try.

How to find text in a range of cells?
 

"RagDyer" wrote ...
Say your datalist was in A1 to E20.

Enter the value you're looking to find into F1,
and try this *array* formula:

=ADDRESS(MAX((A1:E20=F1)*ROW(A1:E20)),MAX((A1:E20= F1)*COLUMN(A1:E20)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

Just to follow up ... That works exactly as I needed.
Thanks again.

How to find text in a range of cells?
 

You're welcome, and thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"r.e.s." wrote in message
...
"RagDyer" wrote ...
Say your datalist was in A1 to E20.

Enter the value you're looking to find into F1,
and try this *array* formula:

=ADDRESS(MAX((A1:E20=F1)*ROW(A1:E20)),MAX((A1:E20= F1)*COLUMN(A1:E20)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

Just to follow up ... That works exactly as I needed.
Thanks again.