trouble with text lookup ... help please

[William DeLeo]

Greetings,

I have a list of the following format:

L1-100001
L1-100002
L1-100003
L2-100001
L3-100001
L3-100002
L4-100001, etc.

I need to find a way to extract from this list (with a cell formula o
series of cell formulae) the highest index for a particular prefi
(i.e. 100003 for L1, or 100002 for L2).

I've tried so many different things and I just can't get any to wor
correctly. I'm familiar with arrays, and VLOOKUP and so forth but
must be doing something wrong.

I am blowing the budget on this task and I really need to move on ..
please help.

TIA
William DeLe

 

[Ron Coderre]

William

See if this gets you on the right track.

With your list in Col_A

C1: (the prefix parameter, eg L2)

D1: =MAX(IF(LEFT(A1:A10,2)=$C$1,--RIGHT(A1:A10,6)))
OR
D1
=MAX(IF(LEFT(A1:A10,2)=$C$1,--MID(A1:A10,SEARCH("-",A1:A10)+1,255)))

Note_1: Those are ARRAY FORMULAS. For array formulas, hold down [Ctrl
and [Shift] when you press [Enter], instead of just pressing [Enter].

Note_2: In case text wrap impacts the display, there are NO spaces i
those formulas.

Is that something you can work with?

Regards,
Ro

 

[William DeLeo]

That totaally works!!! Thank you so much! I tried so many things lik
that but to no avail.

So, what's the story with the "--" preceding RIGHT function? I trie
pretty much the exact same thing but without the "--".

Again ... THANK YOU!!!

Have a nice weekend (I know I will, now).

Bill

 

[Ron Coderre]

William:

The dbl-minus-sign (--) is a usage convention that causes Excel t
coerce a text string or boolean value (true/false) into a numeri
value. When a math operator is applied to a value, Excel attempts t
convert that value to whatever type it needs for the formula to retur
a proper value.

It works this way:
The negative of a value reverses the sign.
The negative of that value restores the sign.

Example:
RIGHT("W1000",4) returns with the *word* "1000"
-RIGHT("W1000",4) converts "1000" to the number -1000
--RIGHT("W1000",4) converts negative number to 1000

In the case of boolean values, the dbl-neg converts TRUE and FALSE to
and 0, respectively.

You could achieve the same results by multiplying a value by 1, but th
dbl-neg indicates to knowledgable users that a "type conversion" i
being effected.

I hope that helps.

Regards,
Ro

 

[William DeLeo]

awsome ... I thought about the *1 text/number thing, but when I tried it
I must have had something else wrong too.

I like the "--" and I will use it. Thank you again, for the solution
as well as the explanation.

Best wishes!