Lookup question and iferror compatibility

[Ryan Gerry]

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$52),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

 

[T. Valko]

**Maybe** this:

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

 

[Ryan Gerry]

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]

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

 

[T. Valko]

Ok, here's what I would do...

I would extract the letters to another cell then do the "lookup" on that
other cell.

In your sample file:

A5 = W12x35

This array formula** will extract the letters:

=LEFT(A5,MATCH(TRUE,ISNUMBER(--MID(A5,ROW($1:$25),1)),0)-1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Let's assume you insert a new column B with the above formula in B5. Then
you can use this formula to get the price:

=SUMIF(I$2:I$20,B5,J$2:J$20)

 

[Ryan Gerry]

Fantastic! This looks like a great start. Thank you very much.

 

[T. Valko]

You're welcome. Thanks for the feedback!