vlookup numbers and string

jbbusse

New Member
Joined
Aug 28, 2002
Messages
8
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??
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

New Member
Joined
Aug 28, 2002
Messages
8
Once the lot# is lookup, other values based on it are looked up from other worksheets.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2002
Messages
8
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2002
Messages
8
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

Well-known Member
Joined
Apr 29, 2002
Messages
636
Use the Isnumber function to see if the cell contains a number or text ;p
 

Forum statistics

Threads
1,147,681
Messages
5,742,597
Members
423,740
Latest member
JCF_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top