vlookup problems

The bit that makes me worry is the formatted as text, WHY? thats the problem, your formula looks fine.

Exel reports tru number as numbers ie they live on the right of a cell, im guessing your live left which would convert numbers/values to text, not what you want Vlookup will fail,
OK try this insert a column in A ie Original becomes b, b Origibal B becomes c and so on, now type this formula in A where in b is doggy data, = value($B??) and hit enter.Your need to copy down, replace ?? for 1 2 or 3 ehatever.
Now your see the numbers ie 0123 are right aligned, correct and that are not should start with a letter ie A123 these convert as well,=(cell to right ie $B3) This will take over any text to text and fill in the NA's your need to auto filtes in A and select the NA's then =($B?) and now copy all data in A and paste as a value, tis will kill the formulas, now your clear tdelet column B,

Reptype or edit the Vlookup as it will have crashed and be eporting NA's and ref in the formula and copy down, shoul be fine

Rdgs

John

> Vlookup doesn't seem to recognize my stock numbers if they start with any number other than zero.

What result do you get? #N/A?

Aladin

=========

I tried doing that but my stock numbers that begin with a 0 get truncated. e.g. 0132 becomes 132 and I want to be able to enter the stock numbers either manualy typing 0123 or using a combobox that returns a value in a predefined table. Will vlookup not work with text or is there a way to format numbers so that the leading zeros stay in tact without using "custom" format for the format properties?
OK try this insert a column in A ie Original becomes b, b Origibal B becomes c and so on, now type this formula in A where in b is doggy data, = value($B??) and hit enter.Your need to copy down, replace ?? for 1 2 or 3 ehatever. Now your see the numbers ie 0123 are right aligned, correct and that are not should start with a letter ie A123 these convert as well,=(cell to right ie $B3) This will take over any text to text and fill in the NA's your need to auto filtes in A and select the NA's then =($B?) and now copy all data in A and paste as a value, tis will kill the formulas, now your clear tdelet column B, : I'm trying to lookup stock numbers in a named range that have 4 characters and are formated as text. I don't have a problem with any of the stock numbers that start with a zero. Vlookup doesn't seem to recognize my stock numbers if they start with any number other than zero. I'm sure there is a simple explanation for my problem. I have ensured that both the source and the lookup table are both formated with "text" properties and the data validation is not present in either case. Any suggestions would be helpful. : My named range looks something like this

Sort Source Data

VLOOKUP returns the first value less than or equal to the input value, unless you use FALSE, which you have. If your source table is in the order you described, it won't pick up properly as in text terms 0123 comes before 2345 etc. If you highlight your "product" range and sort in ascending order on the stock code, it should work. Might also be an idea to use data validation in C20 and refer to your stock code range so you get the right info. Let me know how you go.

Richard

I tried doing that but my stock numbers that begin with a 0 get truncated. e.g. 0132 becomes 132 and I want to be able to enter the stock numbers either manualy typing 0123 or using a combobox that returns a value in a predefined table. Will vlookup not work with text or is there a way to format numbers so that the leading zeros stay in tact without using "custom" format for the format properties? : The bit that makes me worry is the formatted as text, WHY? thats the problem, your formula looks fine. : Exel reports tru number as numbers ie they live on the right of a cell, im guessing your live left which would convert numbers/values to text, not what you want Vlookup will fail, : OK try this insert a column in A ie Original becomes b, b Origibal B becomes c and so on, now type this formula in A where in b is doggy data, = value($B??) and hit enter.Your need to copy down, replace ?? for 1 2 or 3 ehatever. : Now your see the numbers ie 0123 are right aligned, correct and that are not should start with a letter ie A123 these convert as well,=(cell to right ie $B3) This will take over any text to text and fill in the NA's your need to auto filtes in A and select the NA's then =($B?) and now copy all data in A and paste as a value, tis will kill the formulas, now your clear tdelet column B, : Reptype or edit the Vlookup as it will have crashed and be eporting NA's and ref in the formula and copy down, shoul be fine : Rdgs : : : I'm trying to lookup stock numbers in a named range that have 4 characters and are formated as text. I don't have a problem with any of the stock numbers that start with a zero. Vlookup doesn't seem to recognize my stock numbers if they start with any number other than zero. I'm sure there is a simple explanation for my problem. I have ensured that both the source and the lookup table are both formated with "text" properties and the data validation is not present in either case. Any suggestions would be helpful.

Re: Sort Source Data

It still doesn't seem to work. I tried sorting and it wouldn't sort correctly. It will sort (in ascending order) so that the first product number will be 1000 and then up to 9999 and then it will start with the 0001. After that sort it will still pick up stock numbers that are preceded by 0 e.g. 0123 but will not find 1000 or any number with a number greater than 0 in the first character. I also inserted an "A" in front of the stock number so that I have numbers like A1000 and A0123. This seemed to work but I need to match a predefined set of numbers that are not in that format. I'm wondering if I can write some vba code that will scan that cell and do a direct comparison of my table to accomplish the same task. I'm sure that I'm doing something wrong, but I'm just not sure what it is.

John VLOOKUP returns the first value less than or equal to the input value, unless you use FALSE, which you have. If your source table is in the order you described, it won't pick up properly as in text terms 0123 comes before 2345 etc. If you highlight your "product" range and sort in ascending order on the stock code, it should work. Might also be an idea to use data validation in C20 and refer to your stock code range so you get the right info. Let me know how you go. Richard : I tried doing that but my stock numbers that begin with a 0 get truncated. e.g. 0132 becomes 132 and I want to be able to enter the stock numbers either manualy typing 0123 or using a combobox that returns a value in a predefined table. Will vlookup not work with text or is there a way to format numbers so that the leading zeros stay in tact without using "custom" format for the format properties? :

Try using Index & Match functions

As stated in previous post (6200b.html)

Use Index & Match functions together.

Match returns the row number of a lookup value from a one-column lookup table:
=MATCH(lookup_value,lookup_array,match_type)
=MATCH(A1,$C$1:$C$100,FALSE)
where FALSE tells it to find an exact match

Index works like Vlookup, but needn't be sorted:
=INDEX(reference,row_num,column_num)
where reference = full lookup table, and
row_num = uses results of MATCH formula