Lookup question and iferror compatibility
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
|