Is LOOKUP the correct function for this

  • Thread starter Thread starter nigel p
  • Start date Start date
N

nigel p

I wish to have Excel 2000 look along a row of letters and return the
highest letter (latest in the alphabet) to a cell at the left in the
same row. How do I do this?

Example:

Result Entries in columns
F A B C D E F
D A B C D

Thank you in anticipation
 
nigel p said:
I wish to have Excel 2000 look along a row of letters and return the
highest letter (latest in the alphabet) to a cell at the left in the
same row. How do I do this?

Example:

Result Entries in columns
F A B C D E F
D A B C D
Click to expand...

If they're all single letters, try something like the array formula

=CHAR(MAX(IF(C2:IV2<>"",CODE(C2:IV2))))

If they could be arbitrary strings, try the array formula

=INDEX(C2:IV2,MATCH(MAX(COUNTIF(C2:IV2,"<"&C2:IV2)),
COUNTIF(C2:IV2,"<"&C2:IV2),0))
 
Thank you Harlan but neither give me the right result. The first
gives me #value and the second gives me #N/A. I am not experienced in
excel and do not know how to experiment with formulas like these. Can
you help any more?
 
Use Harlan's code, but enter the equation with Ctrl+Shift+Enter at the same
time.
This is an Array formula.

Instead of just Enter, hit Ctrl+Shift+Enter.
 
could a HLOOKUP be used in this case?
Click to expand...

Try it. I mean this in all seriousness. If you get it to do what the OP wants,
show everyone your discovery. If you can't, you'll still learn something from
trying.
 
Back
Top