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 calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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?
 

arapone

New Member
Joined
May 3, 2006
Messages
34

ADVERTISEMENT

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
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

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)
 

arapone

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

arapone

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

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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
 

Forum statistics

Threads
1,136,368
Messages
5,675,358
Members
419,565
Latest member
Phil57

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
Top