vlookup numbers and string

[jbbusse]

I am using vlookup to lookup an item# for a lot#. sometimes the item# is just numbers and sometimes numbers and 1-2 letters, the letters can be at the begining or end of the item#. Once I lookup a number with letters I get #value!.
How do I get around this??

 

[Cam]

Try using Index and Match instead of a vlookup.

 

[IML]

On 2002-08-29 11:29, jbbusse wrote:
I am using vlookup to lookup an item# for a lot#. sometimes the item# is just numbers and sometimes numbers and 1-2 letters, the letters can be at the begining or end of the item#. Once I lookup a number with letters I get #value!.
How do I get around this??

So based on lot#, you look up an item #. The #value! error is typically the result of performing an arithmatic operaton on text. Like in =a1+a2 where A1 is "bird", will result the value error. What are you trying to do with item # when you get it from the vlookup formula?

 

[jbbusse]

Once the lot# is lookup, other values based on it are looked up from other worksheets.

 

[Aladin Akyurek]

You could provide the formula you use, a few examples of lookup values, and a few rows of the lookup table you are using.

 

[jbbusse]

Work
Center Lot# Cat#
CNV1 0230249 1110
KOCH 0210211 1443
DV2/1* 0220678 1539
DV2/1* 0220699 1540
DV3 0220674 1797
DV2 0220684 298
DV3 0220726 2544
#VALUE! 0285050 4060AS
#VALUE! 0285053 4067AS
#VALUE! 0285047 8242A
The user will input the Lot# in and the workcenter and cat# will be looked up. There are several more columns that do lookups based on either the cat#, lot# or workcenter.

Formula for workcenter is:
=IF(C2="","",VLOOKUP(VALUE(C2),sd2,3,FALSE))
for cat# was:
=IF(B2=0,"",VLOOKUP(B2,lotsearch,2,FALSE))
for cat# now:
=INDEX(lotsearch!$B$1:$B$187,MATCH(B2,lotsearch!$A$1:$A$187,0))
This works in the cat# column but now I must change all my other formulas which are also vlookups.
Any help is greatly appreciated!

 

[Ian Mac]

Welcome to the board,

Sorry it has to be said "MY what a long post you've got"

First you dont need VALUE() in your lookup as the text string will never be a value and throw back the error your experiencing.

Your formula should be:

=IF(C2="","",VLOOKUP(C2,sd2,3,FALSE))

I'm assuming the sd2 is a named range of the table!?

Second if you try to look up Cat# and want to return the Work Centre and it has a #VALUE! as your example it will return exactly that.

hope this helps

 

[jbbusse]

I need the VALUE in the formula because the cat# column is a lookup of Lot#. In VBA I could have the result of the formula put in the cell rather than the formula, but as the lot# and other inputable data are added, I want the user to see these values.
The #VALUE error is the result of the formula.
It seems more that I need a way for the formula to know if Value should be there or not? On other sheets I have vlookups that lookup CNV1 with no problem.

 

[Ian Mac]

On 2002-08-29 13:36, jbbusse wrote:
I need the VALUE in the formula because the cat# column is a lookup of Lot#. In VBA I could have the result of the formula put in the cell rather than the formula, but as the lot# and other inputable data are added, I want the user to see these values.
The #VALUE error is the result of the formula.
It seems more that I need a way for the formula to know if Value should be there or not? On other sheets I have vlookups that lookup CNV1 with no problem.

I'm little unsure of what you mean?
could you explain fully what the process is please,

How do you get the data into the columns of the table?
What formula are you using that generates the #Value! in col A?
What do you mean by Cat# is a lookup of Lot#?
what is sd2 in your formula?

Regards

 

[zacemmel]

Use the Isnumber function to see if the cell contains a number or text ;p