How to find text in a range of cells?

R

r.e.s.

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?
 
R

RagDyer

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

r.e.s.

r.e.s. said:
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?
Click to expand...

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.
Click to expand...

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

r.e.s.

RagDyer said:
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.
Click to expand...

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