Find max number of character and return cell address

I have a range of cells from E18:E21 that look like this:

?
?
?3?
?3?4?

I want to return the cell address of the cell which has the max numbers of
"?". So its like a lookup on the range E18:E21 with the search argument
looking for the most number of "?". But then I want to return the cell
address E21.

Thanks

Is that your ultimate aim or are you looking to do something else with the result?

You could try

=ADDRESS(ROW(E$18)+MATCH(MAX(LEN(E18:E21)-LEN(SUBSTITUTE(E18:E21,"?",""))),LEN(E18:E21)-LEN(SUBSTITUTE(E18:E21,"?","")),0)-1,COLUMN($E18),4)

confirmed with CTRL+SHIFT+ENTER

If there's a tie it returns the first cell

No I am pulling the address into another formula after it is calculated. Why?

Thanks

EM



"daddylonglegs" wrote:

>
> Is that your ultimate aim or are you looking to do something else with
> the result?
>
> You could try
>
> =ADDRESS(ROW(E$18)+MATCH(MAX(LEN(E18:E21)-LEN(SUBSTITUTE(E18:E21,"?",""))),LEN(E18:E21)-LEN(SUBSTITUTE(E18:E21,"?","")),0)-1,COLUMN($E18),4)
>
> confirmed with CTRL+SHIFT+ENTER
>
> If there's a tie it returns the first cell
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=533076
>
>

Some functions, INDEX for instance, can return a cell reference or that cell's contents. The INDEX formula on its own will always return the contents but used in the right context within another formula it can return the cell reference.

If you want to use the result of the formula I posted in another formula you'd have to do that in conjunction with INDIRECT

On Fri, 14 Apr 2006 16:13:02 -0700, ExcelMonkey
<[email protected]> wrote:

>I have a range of cells from E18:E21 that look like this:
>
>?
>?
>?3?
>?3?4?
>
>I want to return the cell address of the cell which has the max numbers of
>"?". So its like a lookup on the range E18:E21 with the search argument
>looking for the most number of "?". But then I want to return the cell
>address E21.
>
>Thanks

If the range in which these cells that contain "?" is named rng, then the
**array** formula will return the address of the cell with the most ?'s. If
there are multiple cells with the same highest number of question marks, it
will only return the address of the first cell.

To enter an array formula, you must hold down <ctrl><shift> while hitting
<enter> Excel will place braces {...} around the formula.

Presumes that your array is a single column:

=ADDRESS(MATCH(MAX(LEN(rng)-LEN(SUBSTITUTE(
rng,"?",""))),LEN(rng)-LEN(SUBSTITUTE(rng,"?","")),0)-1
+ROW(rng),COLUMN(rng))


--ron

A simple way to see how many "?" are in each cell, enter this formula in F18 & copy down:
=LEN(E18)-LEN(SUBSTITUTE(E18,"?",""))

This will let you sort them and see the duplicates