**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