vlookup numbers and string

jbbusse

New Member
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try using Index and Match instead of a vlookup.

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?

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

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

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!

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

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.

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

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

Replies
1
Views
859
Replies
14
Views
289
Replies
2
Views
103
Replies
14
Views
359
Replies
5
Views
110

Threads
1,216,381
Messages
6,130,297
Members
449,570
Latest member
TomMacca52

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

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