# String Searching

#### arapone

##### New Member
Alright, I am not the most proficient excel user, but do have some decent knowledge of the program and functions. I am having difficulty with one particular search. I want to look up the greatest value in a row and display that value in a particular cell. The row can contain any letter from A-Z and as well - when a value is non-applicable. How can i do a search to display the highest value? I have tried using if statements going greater to and so forth, but its getting quite ridiculous when in certain applications, there are 50+ rows to search.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Maybe this?

E2 =VLOOKUP(MAX(A1:A11),A1:B11,2,0)
Book16
ABCDE
11value 1
22value 2Returnedvalue 5
33value 3
4100value 4
5200value 5
6Avalue 6
7Zvalue 7
8150value 8
9115value 9
104value 10
116value 11
Sheet1

It's not immediately clear to me what you're trying to do. Are you trying to fill in AI6 with the rightmost letter in AJ:AO?

So,
AI6 = A
AI7 = B
...
AI26 = A
etc.

Or, do you want the first (alphabetically) letter in the range? Or something else?

not quite the right most, The actual file is quite large and this is just a small portion of it. It is a monitoring list for blueprints which are submitted to an engineering firm. We have been having some major issues in tracking the latest submission letter or revision and sometimes the highest letter could be not the latest (ie a resubmission of an older drawing). So the idea here is to search for the higest value and return that value into AI6.

I.e. AI6 =D
AI7 =D
AI8 = -
AI9 = A

its actually the left most which would be the greater

Try:

AI6=CHAR(LARGE(CODE(AJ6:AO6),1))

which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

Beauty!. Works great. Now the next question would be, is it possible to display that value as you had calculated, as well display the contents of row 5 which coresponds to the greatest value. Is there any possiblity of putting it in the same cell or in AH

I.e. AI6 = D 06/08/22

The idea behind that would be:

The latest drawing revision submttied was......
The latest drawing revision was submitted on.....

all that displayed at the beginign of the data... if that makes sense..

Sure.

Code:
``="The latest drawing revision ("&CHAR(LARGE(CODE(AJ6:AO6),1))&") was submitted on "&TEXT(INDEX(AJ\$5:AO\$5,MATCH(CHAR(LARGE(CODE(AJ6:AO6),1)),AJ6:AO6,0)),"mm/dd/yy")``
still confirmed with CTRL+SHIFT+ENTER as before

Replies
3
Views
168
Replies
2
Views
1K
Replies
9
Views
159
Replies
2
Views
125
Replies
1
Views
221

1,219,960
Messages
6,151,163
Members
451,012
Latest member
OH650R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back