| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limitation to Number Format in VLOOKUP?
The following is an example of the numbering format that I am using to
identify items and retrieve data in another worksheet using the VLOOKUP function. One reason that the number sequence is not working may be related to the gaps between each number. Or is it a limitation in the VLOOKUP program to use the number of decimal places that I am using? Any help with an explanation and a solution would be appreciated. Thank You, JAD Numbering Sequence/Method: 01.01001 01.01002 01.01003 01.02001 01.03001 01.04001 01.05001 01.05002 01.07001 01.07002 01.08001 01.08002 01.09001 01.09002 01.10001 01.11001 01.12001 01.12002 01.12003 01.13001 01.14001 01.15001 01.16001 01.16002 01.16003 01.16004 01.16005 01.16006 01.16007 01.16008 01.16009 01.16010 01.17001 01.17002 01.17003 01.17004 01.17005 01.18001 01.19001 01.19002 01.19003 01.19004 01.20001 01.20002 01.20003 01.20004 01.21001 01.21002 01.21003 01.21004 01.22001 01.23001 01.23002 01.24001 01.24002 01.24003 01.24004 01.25001 01.26001 01.27001 01.28001 01.29001 01.29002 01.29003 01.29004 01.30001 01.31001 01.32001 01.33001 01.33002 01.33003 01.34001 01.36001 01.36002 01.37001 01.38001 01.38002 01.39001 01.39002 01.40001 01.40002 01.41001 01.41002 01.42001 01.43001 01.44001 01.45001 01.45002 01.46001 01.47001 01.47002 01.48001 01.48002 01.49001 01.50001 01.51001 01.52001 01.53001 01.53002 01.53003 01.54001 01.55001 01.55002 01.56001 01.57001 01.57002 01.57003 01.58001 01.59001 01.59002 01.59003 01.60001 01.61001 01.61002 01.61003 01.62001 01.63001 01.63002 01.65001 01.65002 01.66001 01.67001 01.67002 01.68001 01.69001 01.70001 01.71001 01.72001 01.72002 01.73001 01.73002 01.74001 01.75001 01.75002 01.76001 01.77001 01.78001 01.79001 01.80001 01.80002 01.81001 01.81002 01.82001 01.82002 01.83001 01.83002 01.84001 01.84002 01.85001 01.86001 01.87001 01.88001 01.88002 01.88003 01.88004 01.89001 01.89002 01.89003 01.90001 01.90002 01.90003 01.90004 01.90005 01.90006 01.90007 01.90008 01.90009 01.90010 01.90011 01.90012 01.90013 01.90014 01.90015 01.90016 01.91001 01.91002 01.91003 01.91004 01.91005 01.91006 01.91007 01.91008 01.91009 01.91010 01.91011 01.91012 01.91013 01.91014 01.91015 01.91016 01.91017 01.91018 01.91019 01.91020 01.91021 01.91022 01.91023 01.91024 01.92001 01.92002 01.92003 01.92004 01.92005 01.92006 01.92007 01.92008 01.92009 01.92010 01.93001 02.01001 02.01002 02.01003 02.02001 02.03001 02.04001 02.05001 02.07001 02.07002 02.08001 02.08002 02.09001 02.09002 02.09003 02.09004 02.09005 02.09006 02.09007 02.09008 02.09009 02.09010 02.10001 02.10002 02.10003 02.10004 02.10005 01.18001 02.12001 02.12002 02.12003 02.12004 02.13001 02.13002 02.13003 02.13004 02.14001 02.14002 02.14003 02.14004 02.15001 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Limitation to Number Format in VLOOKUP?
Hi JAD,
Because the numbers have leading zeros then I assume that they are in text format; not numerical. When using VLOOKUP, the lookup value must also be in text format in the cell where you are referencing the value or converted to text in the Vlookup formula. Use double quotes if entered directly in the formula. Example:- Your list in column A Cell B1 contains numerical 1.11001 Formula (converting B1 to text format):- =VLOOKUP(TEXT(B1,"00.00000"),$A$1:$A$249,1,FALSE) Formula if entering value directly in formula:- =VLOOKUP("01.05002",$A$1:$A$249,1,FALSE) If this does not answer your question, then post a copy of the foumula which is not working. -- Regards, OssieMac "JAD" wrote: The following is an example of the numbering format that I am using to identify items and retrieve data in another worksheet using the VLOOKUP function. One reason that the number sequence is not working may be related to the gaps between each number. Or is it a limitation in the VLOOKUP program to use the number of decimal places that I am using? Any help with an explanation and a solution would be appreciated. Thank You, JAD Numbering Sequence/Method: 01.01001 01.01002 01.01003 01.02001 01.03001 01.04001 01.05001 01.05002 01.07001 01.07002 01.08001 01.08002 01.09001 01.09002 01.10001 01.11001 01.12001 01.12002 01.12003 01.13001 01.14001 01.15001 01.16001 01.16002 01.16003 01.16004 01.16005 01.16006 01.16007 01.16008 01.16009 01.16010 01.17001 01.17002 01.17003 01.17004 01.17005 01.18001 01.19001 01.19002 01.19003 01.19004 01.20001 01.20002 01.20003 01.20004 01.21001 01.21002 01.21003 01.21004 01.22001 01.23001 01.23002 01.24001 01.24002 01.24003 01.24004 01.25001 01.26001 01.27001 01.28001 01.29001 01.29002 01.29003 01.29004 01.30001 01.31001 01.32001 01.33001 01.33002 01.33003 01.34001 01.36001 01.36002 01.37001 01.38001 01.38002 01.39001 01.39002 01.40001 01.40002 01.41001 01.41002 01.42001 01.43001 01.44001 01.45001 01.45002 01.46001 01.47001 01.47002 01.48001 01.48002 01.49001 01.50001 01.51001 01.52001 01.53001 01.53002 01.53003 01.54001 01.55001 01.55002 01.56001 01.57001 01.57002 01.57003 01.58001 01.59001 01.59002 01.59003 01.60001 01.61001 01.61002 01.61003 01.62001 01.63001 01.63002 01.65001 01.65002 01.66001 01.67001 01.67002 01.68001 01.69001 01.70001 01.71001 01.72001 01.72002 01.73001 01.73002 01.74001 01.75001 01.75002 01.76001 01.77001 01.78001 01.79001 01.80001 01.80002 01.81001 01.81002 01.82001 01.82002 01.83001 01.83002 01.84001 01.84002 01.85001 01.86001 01.87001 01.88001 01.88002 01.88003 01.88004 01.89001 01.89002 01.89003 01.90001 01.90002 01.90003 01.90004 01.90005 01.90006 01.90007 01.90008 01.90009 01.90010 01.90011 01.90012 01.90013 01.90014 01.90015 01.90016 01.91001 01.91002 01.91003 01.91004 01.91005 01.91006 01.91007 01.91008 01.91009 01.91010 01.91011 01.91012 01.91013 01.91014 01.91015 01.91016 01.91017 01.91018 01.91019 01.91020 01.91021 01.91022 01.91023 01.91024 01.92001 01.92002 01.92003 01.92004 01.92005 01.92006 01.92007 01.92008 01.92009 01.92010 01.93001 02.01001 02.01002 02.01003 02.02001 02.03001 02.04001 02.05001 02.07001 02.07002 02.08001 02.08002 02.09001 02.09002 02.09003 02.09004 02.09005 02.09006 02.09007 02.09008 02.09009 02.09010 02.10001 02.10002 02.10003 02.10004 02.10005 01.18001 02.12001 02.12002 02.12003 02.12004 02.13001 02.13002 02.13003 02.13004 02.14001 02.14002 02.14003 02.14004 02.15001 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Limitation to Number Format in VLOOKUP?
Hi again JAD,
I should also have added that if the numbers are numerical and formatted to "00.00000" then the next question is are the values the actual values or are they simply formatted to display 5 decimal places? If they actually have more decimal places than displayed then vlookup will not find them. You could then look at Round function so they only have 5 decimal places. -- Regards, OssieMac "JAD" wrote: The following is an example of the numbering format that I am using to identify items and retrieve data in another worksheet using the VLOOKUP function. One reason that the number sequence is not working may be related to the gaps between each number. Or is it a limitation in the VLOOKUP program to use the number of decimal places that I am using? Any help with an explanation and a solution would be appreciated. Thank You, JAD Numbering Sequence/Method: 01.01001 01.01002 01.01003 01.02001 01.03001 01.04001 01.05001 01.05002 01.07001 01.07002 01.08001 01.08002 01.09001 01.09002 01.10001 01.11001 01.12001 01.12002 01.12003 01.13001 01.14001 01.15001 01.16001 01.16002 01.16003 01.16004 01.16005 01.16006 01.16007 01.16008 01.16009 01.16010 01.17001 01.17002 01.17003 01.17004 01.17005 01.18001 01.19001 01.19002 01.19003 01.19004 01.20001 01.20002 01.20003 01.20004 01.21001 01.21002 01.21003 01.21004 01.22001 01.23001 01.23002 01.24001 01.24002 01.24003 01.24004 01.25001 01.26001 01.27001 01.28001 01.29001 01.29002 01.29003 01.29004 01.30001 01.31001 01.32001 01.33001 01.33002 01.33003 01.34001 01.36001 01.36002 01.37001 01.38001 01.38002 01.39001 01.39002 01.40001 01.40002 01.41001 01.41002 01.42001 01.43001 01.44001 01.45001 01.45002 01.46001 01.47001 01.47002 01.48001 01.48002 01.49001 01.50001 01.51001 01.52001 01.53001 01.53002 01.53003 01.54001 01.55001 01.55002 01.56001 01.57001 01.57002 01.57003 01.58001 01.59001 01.59002 01.59003 01.60001 01.61001 01.61002 01.61003 01.62001 01.63001 01.63002 01.65001 01.65002 01.66001 01.67001 01.67002 01.68001 01.69001 01.70001 01.71001 01.72001 01.72002 01.73001 01.73002 01.74001 01.75001 01.75002 01.76001 01.77001 01.78001 01.79001 01.80001 01.80002 01.81001 01.81002 01.82001 01.82002 01.83001 01.83002 01.84001 01.84002 01.85001 01.86001 01.87001 01.88001 01.88002 01.88003 01.88004 01.89001 01.89002 01.89003 01.90001 01.90002 01.90003 01.90004 01.90005 01.90006 01.90007 01.90008 01.90009 01.90010 01.90011 01.90012 01.90013 01.90014 01.90015 01.90016 01.91001 01.91002 01.91003 01.91004 01.91005 01.91006 01.91007 01.91008 01.91009 01.91010 01.91011 01.91012 01.91013 01.91014 01.91015 01.91016 01.91017 01.91018 01.91019 01.91020 01.91021 01.91022 01.91023 01.91024 01.92001 01.92002 01.92003 01.92004 01.92005 01.92006 01.92007 01.92008 01.92009 01.92010 01.93001 02.01001 02.01002 02.01003 02.02001 02.03001 02.04001 02.05001 02.07001 02.07002 02.08001 02.08002 02.09001 02.09002 02.09003 02.09004 02.09005 02.09006 02.09007 02.09008 02.09009 02.09010 02.10001 02.10002 02.10003 02.10004 02.10005 01.18001 02.12001 02.12002 02.12003 02.12004 02.13001 02.13002 02.13003 02.13004 02.14001 02.14002 02.14003 02.14004 02.15001 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup limitation | Excel Worksheet Functions | |||
| Custom Format Limitation | Excel Discussion (Misc queries) | |||
| VLOOKUP Limitation and Solution? | Excel Worksheet Functions | |||
| VLOOKUP Problem (limitation)? | Excel Worksheet Functions | |||
| vlookup limitation | Excel Discussion (Misc queries) | |||
Linear Mode
