Find max number of character and return cell address

E

ExcelMonkey

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
 
D

daddylonglegs

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
 
E

ExcelMonkey

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

Thanks

EM
 
D

daddylonglegs

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
 
R

Ron Rosenfeld

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

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
 
J

John James

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
 
Top