| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup bug?
I need help in obtaining consistent vlookup function results. In most cases
i receive the accurate return. However, on some returns I get the wrong value. The value returned belongs to the same column number but a different row. Formula auditing does not provide where the last value comes from, so i can't trace from there. The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list created through "DATA/VALIDATION" menu. This particular line should return a value in column 5, row 32; instead it returns data from row 22. I've changed column number but it still returns data from row 22. What am I doing wrong? As I read someone else say: I've been pulling my hair for the last 3 hours! Please help! Many thanks, David |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup bug?
=VLOOKUP(C23,AE5:AQ215,5,0)
"David G." wrote: I need help in obtaining consistent vlookup function results. In most cases i receive the accurate return. However, on some returns I get the wrong value. The value returned belongs to the same column number but a different row. Formula auditing does not provide where the last value comes from, so i can't trace from there. The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list created through "DATA/VALIDATION" menu. This particular line should return a value in column 5, row 32; instead it returns data from row 22. I've changed column number but it still returns data from row 22. What am I doing wrong? As I read someone else say: I've been pulling my hair for the last 3 hours! Please help! Many thanks, David |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup bug?
Are you looking for an exact match, if so use
=VLOOKUP(C23,AE5:AQ215,5,0) If you get #N/A then there is no match -- Regards, Peo Sjoblom wrote in message ... I need help in obtaining consistent vlookup function results. In most cases i receive the accurate return. However, on some returns I get the wrong value. The value returned belongs to the same column number but a different row. Formula auditing does not provide where the last value comes from, so i can't trace from there. The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list created through "DATA/VALIDATION" menu. This particular line should return a value in column 5, row 32; instead it returns data from row 22. I've changed column number but it still returns data from row 22. What am I doing wrong? As I read someone else say: I've been pulling my hair for the last 3 hours! Please help! Many thanks, David |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup bug?
Worked like a charmer. What did i tell Excel to do when i added the "0"?
"Peo Sjoblom" wrote: Are you looking for an exact match, if so use =VLOOKUP(C23,AE5:AQ215,5,0) If you get #N/A then there is no match -- Regards, Peo Sjoblom wrote in message ... I need help in obtaining consistent vlookup function results. In most cases i receive the accurate return. However, on some returns I get the wrong value. The value returned belongs to the same column number but a different row. Formula auditing does not provide where the last value comes from, so i can't trace from there. The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list created through "DATA/VALIDATION" menu. This particular line should return a value in column 5, row 32; instead it returns data from row 22. I've changed column number but it still returns data from row 22. What am I doing wrong? As I read someone else say: I've been pulling my hair for the last 3 hours! Please help! Many thanks, David |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup bug?
To look for an exact match, if you look in help you will see that they use
either FALSE or TRUE, FALSE is equal to 0, TRUE is equal to 1 or omitted like in your case. TRUE or omitted means the values need to be sorted in ascending order and if it doesn't find the match it will look for the largest values less than or equal to the lookup value whereas using FALSE or 0 it will return #N/A if it cannot find a match -- Regards, Peo Sjoblom "David G." wrote in message ... Worked like a charmer. What did i tell Excel to do when i added the "0"? "Peo Sjoblom" wrote: Are you looking for an exact match, if so use =VLOOKUP(C23,AE5:AQ215,5,0) If you get #N/A then there is no match -- Regards, Peo Sjoblom wrote in message ... I need help in obtaining consistent vlookup function results. In most cases i receive the accurate return. However, on some returns I get the wrong value. The value returned belongs to the same column number but a different row. Formula auditing does not provide where the last value comes from, so i can't trace from there. The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list created through "DATA/VALIDATION" menu. This particular line should return a value in column 5, row 32; instead it returns data from row 22. I've changed column number but it still returns data from row 22. What am I doing wrong? As I read someone else say: I've been pulling my hair for the last 3 hours! Please help! Many thanks, David |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup bug?
The 0 represents FALSE. By default, VLOOKUP will find the closest match.
The 0 or FALSE forces VLOOKUP to find an exact match. And like Peo said, the #N/A was an indication that there was no match. So you may want to check that too. HTH, Paul "David G." wrote in message ... Worked like a charmer. What did i tell Excel to do when i added the "0"? "Peo Sjoblom" wrote: Are you looking for an exact match, if so use =VLOOKUP(C23,AE5:AQ215,5,0) If you get #N/A then there is no match -- Regards, Peo Sjoblom wrote in message ... I need help in obtaining consistent vlookup function results. In most cases i receive the accurate return. However, on some returns I get the wrong value. The value returned belongs to the same column number but a different row. Formula auditing does not provide where the last value comes from, so i can't trace from there. The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list created through "DATA/VALIDATION" menu. This particular line should return a value in column 5, row 32; instead it returns data from row 22. I've changed column number but it still returns data from row 22. What am I doing wrong? As I read someone else say: I've been pulling my hair for the last 3 hours! Please help! Many thanks, David |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup bug?
Thank you!
David "PCLIVE" wrote: The 0 represents FALSE. By default, VLOOKUP will find the closest match. The 0 or FALSE forces VLOOKUP to find an exact match. And like Peo said, the #N/A was an indication that there was no match. So you may want to check that too. HTH, Paul "David G." wrote in message ... Worked like a charmer. What did i tell Excel to do when i added the "0"? "Peo Sjoblom" wrote: Are you looking for an exact match, if so use =VLOOKUP(C23,AE5:AQ215,5,0) If you get #N/A then there is no match -- Regards, Peo Sjoblom wrote in message ... I need help in obtaining consistent vlookup function results. In most cases i receive the accurate return. However, on some returns I get the wrong value. The value returned belongs to the same column number but a different row. Formula auditing does not provide where the last value comes from, so i can't trace from there. The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list created through "DATA/VALIDATION" menu. This particular line should return a value in column 5, row 32; instead it returns data from row 22. I've changed column number but it still returns data from row 22. What am I doing wrong? As I read someone else say: I've been pulling my hair for the last 3 hours! Please help! Many thanks, David |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
