About the only other thing I can think of is to extract the first few
letters then lookup based on those. You can either extract the first few
letters to another cell or do it in the formula.

For example...

Lookup value = BC4x4

Extract "BC" then go from there.

A1x23
BC4x4
AAG2x3
BC2x1

Do all the lookup values follow that same pattern? Various letters followed
by *a single digit followed by x* ?


--
Biff
Microsoft Excel MVP


"Ryan Gerry" wrote in message
...
Biff's idea didn't work, although it did start me down another logic path
that I am still working on. Are there another thoughts ideas you experts
have? Keep in mind that "AAG" cannot return the value next to "A".

Ryan

"T. Valko" wrote in message
...
**Maybe** this:

=LOOKUP(1E100,SEARCH(V$34:V$54,D5),W$34:W$54)

--
Biff
Microsoft Excel MVP


"Ryan Gerry" wrote in message
...
Experts,

I am currently using a function that seems too complicated and will not
work on excel 2003. What I am trying to accomplish is to match the first
1 to 5 characters (strings contain 1-5 letters then sets of numbers
separated by "x" I am only interested in the first 1-5 letters) from one
table to a second table (on the same sheet) and return the value in the
next column. This is what I am currently doing:

=IFERROR(LOOKUP(LEFT(D6,5),$V$34:$V$52,$W$34:$W$52 ),IFERROR(LOOKUP(LEFT(D6,4),$V$34:$V$52,$W$34:$W$5 2),IFERROR(LOOKUP(LEFT(D6,3),$V$34:$V$52,$W$34:$W$ 52),IFERROR(LOOKUP(LEFT(D6,2),$V$34:$V$52,$W$34:$W $52),IFERROR(LOOKUP(LEFT(D6,1),$V$34:$V$52,$W$34:$ W$52),0)))))

Table 1
D E E(output)
5 A1x23 <equation 1
6 BC4x4 <equation 3
7 AAG2x3 <equation 2
8 BC2x1 <equation 3

Table 2
V W
34 A 1
35 AAG 2
36 BC 3
... ... ...
54 AECD 4

I would like to accomplish the same task while being compatible with
2003. Making the formula more straight forward would be a plus however
isn't entirely necessary.

Thank you
Ryan