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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=533076

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=533076

On Fri, 14 Apr 2006 16:13:02 -0700, ExcelMonkey
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


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=533076