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

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

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

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

Yes there is a pattern. There is always a numeric character after the
characters of interest, this was one avenue I was trying to follow but could
not find an appropriate function to differentiate between alpha and numeric
characters. The kicker is that there are "x"s between numbers that are of
little interest to this operation.

I am currently extracting the first 5 characters and looking for an exact
match and if there is not one found I look at the first 4 for an exact match
and so on. This unfortunately makes for a lengthy formula and the best way I
have come to do this is with and IfError which will not operate in '03. I
have attached the actual sheet I am using to hopefully provide further
clarity. I highlighted the sections of interest in yellow. I have removed a
large amount of the sheet for privacy reasons.

Ryan

--
Regards,
Ryan Gerry

Project Engineer
Newport Industrial Fabrication
Phone 207.368.4344 ext. 19
Fax 207.368.5552
Cell 207.852.2664
www.nif-inc.com

"T. Valko" wrote in message
...
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

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)

--
Biff
Microsoft Excel MVP


"Ryan Gerry" wrote in message
...
Yes there is a pattern. There is always a numeric character after the
characters of interest, this was one avenue I was trying to follow but
could
not find an appropriate function to differentiate between alpha and
numeric
characters. The kicker is that there are "x"s between numbers that are of
little interest to this operation.

I am currently extracting the first 5 characters and looking for an exact
match and if there is not one found I look at the first 4 for an exact
match
and so on. This unfortunately makes for a lengthy formula and the best way
I
have come to do this is with and IfError which will not operate in '03. I
have attached the actual sheet I am using to hopefully provide further
clarity. I highlighted the sections of interest in yellow. I have removed
a
large amount of the sheet for privacy reasons.

Ryan

--
Regards,
Ryan Gerry

Project Engineer
Newport Industrial Fabrication
Phone 207.368.4344 ext. 19
Fax 207.368.5552
Cell 207.852.2664
www.nif-inc.com

"T. Valko" wrote in message
...
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

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



"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Ryan Gerry" wrote in message
...
Yes there is a pattern. There is always a numeric character after the
characters of interest, this was one avenue I was trying to follow but
could
not find an appropriate function to differentiate between alpha and
numeric
characters. The kicker is that there are "x"s between numbers that are of
little interest to this operation.

I am currently extracting the first 5 characters and looking for an exact
match and if there is not one found I look at the first 4 for an exact
match
and so on. This unfortunately makes for a lengthy formula and the best
way I
have come to do this is with and IfError which will not operate in '03. I
have attached the actual sheet I am using to hopefully provide further
clarity. I highlighted the sections of interest in yellow. I have removed
a
large amount of the sheet for privacy reasons.

Ryan

--
Regards,
Ryan Gerry

Project Engineer
Newport Industrial Fabrication
Phone 207.368.4344 ext. 19
Fax 207.368.5552
Cell 207.852.2664
www.nif-inc.com

"T. Valko" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ryan Gerry" wrote in message
...
Fantastic! This looks like a great start. Thank you very much.



"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Ryan Gerry" wrote in message
...
Yes there is a pattern. There is always a numeric character after the
characters of interest, this was one avenue I was trying to follow but
could
not find an appropriate function to differentiate between alpha and
numeric
characters. The kicker is that there are "x"s between numbers that are
of
little interest to this operation.

I am currently extracting the first 5 characters and looking for an
exact
match and if there is not one found I look at the first 4 for an exact
match
and so on. This unfortunately makes for a lengthy formula and the best
way I
have come to do this is with and IfError which will not operate in '03.
I
have attached the actual sheet I am using to hopefully provide further
clarity. I highlighted the sections of interest in yellow. I have
removed a
large amount of the sheet for privacy reasons.

Ryan

--
Regards,
Ryan Gerry

Project Engineer
Newport Industrial Fabrication
Phone 207.368.4344 ext. 19
Fax 207.368.5552
Cell 207.852.2664
www.nif-inc.com

"T. Valko" wrote in message
...
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