trouble with text lookup ... help please
 

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


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550365

trouble with text lookup ... help please
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=550365

trouble with text lookup ... help please
 

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


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550365

trouble with text lookup ... help please
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=550365

trouble with text lookup ... help please
 

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!


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550365