software

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
quot;?quot;. So its like a lookup on the range E18:E21 with the search argument
looking for the most number of quot;?quot;. 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,quot;?quot;,quot;quot;))),LEN(E18:E21)-LEN(SUBSTITUTE(E18:E21,quot;?quot;,quot;quot;)),0)-1,COLUMN($E18),4)

confirmed with CTRL SHIFT ENTER

If there's a tie it returns the first cell--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=533076No I am pulling the address into another formula after it is calculated. Why?

Thanks

EM
quot;daddylonglegsquot; wrote:

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


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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=533076On Fri, 14 Apr 2006 16:13:02 -0700, ExcelMonkey
gt; wrote:

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

If the range in which these cells that contain quot;?quot; 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 lt;ctrlgt;lt;shiftgt; while hitting
lt;entergt; Excel will place braces {...} around the formula.

Presumes that your array is a single column:

=ADDRESS(MATCH(MAX(LEN(rng)-LEN(SUBSTITUTE(
rng,quot;?quot;,quot;quot;))),LEN(rng)-LEN(SUBSTITUTE(rng,quot;?quot;,quot;quot;)),0)-1
ROW(rng),COLUMN(rng))--ron


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

This will let you sort them and see the duplicates--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=533076