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 or series of cell formulae) the highest index for a particular prefix (i.e. 100003 for L1, or 100002 for L2).

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

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

TIA
William DeLeo

[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 in those formulas.

Is that something you can work with?

Regards,
Ron

[William DeLeo]

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

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

Again ... THANK YOU!!!

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

Billy

[Ron Coderre]

William:

The dbl-minus-sign (--) is a usage convention that causes Excel to coerce a text string or boolean value (true/false) into a numeric value. When a math operator is applied to a value, Excel attempts to convert that value to whatever type it needs for the formula to return 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 1 and 0, respectively.

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

I hope that helps.

Regards,
Ron

[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!

p.s. (mass huh?)