| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Returns Wrong/No Data
My Data Range is (shortened for this example):
124 00A2RG000024CMNM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1 125 00A2RG000024CLTM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1 126 00A2RG000025CDGM A2RG.CS.1.1.1 A2RG.CS.1.1.1.2 129 00A2RG000026LDGM A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 135 00A2RG000031ICMM A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 137 00A2RG000032CLTM A2RG.CS.1.2.1 A2RG.CS.1.2.1.1 My first formula in line 26 below is: =VLOOKUP(E26,$D$124:$F$149,2), and respectively E27 and E28 My results a 26 #N/A #N/A 00A2RG000024CLTM 27 A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 00A2RG000032CLTM 28 A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 00A2RG000026LDGM As you can see, Line 26 show #N/A, line 27 displays the data from 2 lines above, and line 28 is correct. This is really throwing us off! Can you help? Thanks....TomCat |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Returns Wrong/No Data
Make your formula
=VLOOKUP(E26,$D$124:$F$149,2,FALSE) Look in HELP for the meaning of the 4th argument -- Kind regards, Niek Otten Microsoft MVP - Excel "TomCat" wrote in message ... | My Data Range is (shortened for this example): | 124 00A2RG000024CMNM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1 | 125 00A2RG000024CLTM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1 | 126 00A2RG000025CDGM A2RG.CS.1.1.1 A2RG.CS.1.1.1.2 | 129 00A2RG000026LDGM A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 | 135 00A2RG000031ICMM A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 | 137 00A2RG000032CLTM A2RG.CS.1.2.1 A2RG.CS.1.2.1.1 | | My first formula in line 26 below is: =VLOOKUP(E26,$D$124:$F$149,2), and | respectively E27 and E28 | | My results a | 26 #N/A #N/A 00A2RG000024CLTM | 27 A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 00A2RG000032CLTM | 28 A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 00A2RG000026LDGM | | As you can see, Line 26 show #N/A, line 27 displays the data from 2 lines | above, and line 28 is correct. | | This is really throwing us off! Can you help? Thanks....TomCat | |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Returns Wrong/No Data
You need to include the 4th parameter to VLOOKUP. If this parameter is
FALSE, then VLOOKUP doesn't require a sorted data range and will return a value when (and only when) an exact match is found. If this parameter is omitted or TRUE, VLOOKUP requires that the data range be in sorted order and will return a closest match if an exact match is not found. =VLOOKUP(E26,$D$124:$F$149,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "TomCat" wrote in message ... My Data Range is (shortened for this example): 124 00A2RG000024CMNM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1 125 00A2RG000024CLTM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1 126 00A2RG000025CDGM A2RG.CS.1.1.1 A2RG.CS.1.1.1.2 129 00A2RG000026LDGM A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 135 00A2RG000031ICMM A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 137 00A2RG000032CLTM A2RG.CS.1.2.1 A2RG.CS.1.2.1.1 My first formula in line 26 below is: =VLOOKUP(E26,$D$124:$F$149,2), and respectively E27 and E28 My results a 26 #N/A #N/A 00A2RG000024CLTM 27 A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 00A2RG000032CLTM 28 A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 00A2RG000026LDGM As you can see, Line 26 show #N/A, line 27 displays the data from 2 lines above, and line 28 is correct. This is really throwing us off! Can you help? Thanks....TomCat |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup Returns Wrong/No Data
Y'all are great! Thanks!
TomCat "Chip Pearson" wrote: You need to include the 4th parameter to VLOOKUP. If this parameter is FALSE, then VLOOKUP doesn't require a sorted data range and will return a value when (and only when) an exact match is found. If this parameter is omitted or TRUE, VLOOKUP requires that the data range be in sorted order and will return a closest match if an exact match is not found. =VLOOKUP(E26,$D$124:$F$149,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "TomCat" wrote in message ... My Data Range is (shortened for this example): 124 00A2RG000024CMNM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1 125 00A2RG000024CLTM A2RG.CS.1.1.1 A2RG.CS.1.1.1.1 126 00A2RG000025CDGM A2RG.CS.1.1.1 A2RG.CS.1.1.1.2 129 00A2RG000026LDGM A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 135 00A2RG000031ICMM A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 137 00A2RG000032CLTM A2RG.CS.1.2.1 A2RG.CS.1.2.1.1 My first formula in line 26 below is: =VLOOKUP(E26,$D$124:$F$149,2), and respectively E27 and E28 My results a 26 #N/A #N/A 00A2RG000024CLTM 27 A2RG.CS.1.1.1 A2RG.CS.1.1.1.8 00A2RG000032CLTM 28 A2RG.CS.0.0.0 A2RG.CS.1.1.1.3 00A2RG000026LDGM As you can see, Line 26 show #N/A, line 27 displays the data from 2 lines above, and line 28 is correct. This is really throwing us off! Can you help? Thanks....TomCat |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup occasionally returns formula with no data | Excel Discussion (Misc queries) | |||
| vlookup returns bad data | Excel Worksheet Functions | |||
| Row() function returns wrong row and more.. | Excel Worksheet Functions | |||
| Vlookup gives wrong answers when used in large data. Pls advise? | Excel Worksheet Functions | |||
| Vlookup data wrong if the small value found are same | Excel Discussion (Misc queries) | |||
Linear Mode
