String Searching

arapone

New Member
Joined
May 3, 2006
Messages
34
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top