Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup shows result one cell above the expected result
Hi there,
For some reason, my vlookup formula results in the match that appears a cell above the correct match. The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE) My cells are fomatted to 'General' (not text or number). I've tried sorting, but it did not fix the problem. There are 5 columns on my worksheet (1st column shows asset ID, 2nd column shows corresponding name of the asset, column 3 shows Available Units, column 4 shows the price). Column 5 (E) shows the asset ID's that I am finding a match for. I need to show (as a result) the Available Units once a match between ID's is found. Thanks for your help. Marie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup shows result one cell above the expected result
Are you wanting an *EXACT* match? If so, change the range_lookup argument to
FALSE or 0. =VLOOKUP(E3,A1:D20,3,FALSE) =VLOOKUP(E3,A1:D20,3,0) Biff "marie" wrote in message ... Hi there, For some reason, my vlookup formula results in the match that appears a cell above the correct match. The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE) My cells are fomatted to 'General' (not text or number). I've tried sorting, but it did not fix the problem. There are 5 columns on my worksheet (1st column shows asset ID, 2nd column shows corresponding name of the asset, column 3 shows Available Units, column 4 shows the price). Column 5 (E) shows the asset ID's that I am finding a match for. I need to show (as a result) the Available Units once a match between ID's is found. Thanks for your help. Marie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup shows result one cell above the expected result
What happens if you change "True" to "False" (no quotes)?
-- Regards, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "marie" wrote in message ... Hi there, For some reason, my vlookup formula results in the match that appears a cell above the correct match. The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE) My cells are fomatted to 'General' (not text or number). I've tried sorting, but it did not fix the problem. There are 5 columns on my worksheet (1st column shows asset ID, 2nd column shows corresponding name of the asset, column 3 shows Available Units, column 4 shows the price). Column 5 (E) shows the asset ID's that I am finding a match for. I need to show (as a result) the Available Units once a match between ID's is found. Thanks for your help. Marie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup shows result one cell above the expected result
You should only use VLOOKUP with a 4th argument of TRUE when you have lookup
range sorted ascending and you want to match with the nearest value below. For your purposes I presume you need an exact match, change TRUE to FALSE "marie" wrote: Hi there, For some reason, my vlookup formula results in the match that appears a cell above the correct match. The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE) My cells are fomatted to 'General' (not text or number). I've tried sorting, but it did not fix the problem. There are 5 columns on my worksheet (1st column shows asset ID, 2nd column shows corresponding name of the asset, column 3 shows Available Units, column 4 shows the price). Column 5 (E) shows the asset ID's that I am finding a match for. I need to show (as a result) the Available Units once a match between ID's is found. Thanks for your help. Marie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup shows result one cell above the expected result
When I changed the 4th argument to FALSE or 0, my result shows N/A.
I tried removing the 4th argument, but the result is the same as when I had the 4th arg of TRUE in it. "RagDyeR" wrote: What happens if you change "True" to "False" (no quotes)? -- Regards, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "marie" wrote in message ... Hi there, For some reason, my vlookup formula results in the match that appears a cell above the correct match. The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE) My cells are fomatted to 'General' (not text or number). I've tried sorting, but it did not fix the problem. There are 5 columns on my worksheet (1st column shows asset ID, 2nd column shows corresponding name of the asset, column 3 shows Available Units, column 4 shows the price). Column 5 (E) shows the asset ID's that I am finding a match for. I need to show (as a result) the Available Units once a match between ID's is found. Thanks for your help. Marie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup shows result one cell above the expected result
This usually denotes that your data is not equal in both lists.
To test this, *manually* type in a value in Column E, and *manually* type in the *exact* same value in Column A, so that you *know* that you have a match. Also, make sure both test cells are the same format. What happens now? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "marie" wrote in message ... When I changed the 4th argument to FALSE or 0, my result shows N/A. I tried removing the 4th argument, but the result is the same as when I had the 4th arg of TRUE in it. "RagDyeR" wrote: What happens if you change "True" to "False" (no quotes)? -- Regards, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "marie" wrote in message ... Hi there, For some reason, my vlookup formula results in the match that appears a cell above the correct match. The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE) My cells are fomatted to 'General' (not text or number). I've tried sorting, but it did not fix the problem. There are 5 columns on my worksheet (1st column shows asset ID, 2nd column shows corresponding name of the asset, column 3 shows Available Units, column 4 shows the price). Column 5 (E) shows the asset ID's that I am finding a match for. I need to show (as a result) the Available Units once a match between ID's is found. Thanks for your help. Marie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup shows result one cell above the expected result
I got it. I had the cells formatted the same way, but I tested it further.
Because the values on columns A and E had numbers that starts with '00', for some reason, excel is not recognizing the first 2 digits once the formatting has been changed to general from numeric. Thanks for the help. "Ragdyer" wrote: This usually denotes that your data is not equal in both lists. To test this, *manually* type in a value in Column E, and *manually* type in the *exact* same value in Column A, so that you *know* that you have a match. Also, make sure both test cells are the same format. What happens now? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "marie" wrote in message ... When I changed the 4th argument to FALSE or 0, my result shows N/A. I tried removing the 4th argument, but the result is the same as when I had the 4th arg of TRUE in it. "RagDyeR" wrote: What happens if you change "True" to "False" (no quotes)? -- Regards, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "marie" wrote in message ... Hi there, For some reason, my vlookup formula results in the match that appears a cell above the correct match. The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE) My cells are fomatted to 'General' (not text or number). I've tried sorting, but it did not fix the problem. There are 5 columns on my worksheet (1st column shows asset ID, 2nd column shows corresponding name of the asset, column 3 shows Available Units, column 4 shows the price). Column 5 (E) shows the asset ID's that I am finding a match for. I need to show (as a result) the Available Units once a match between ID's is found. Thanks for your help. Marie |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup shows result one cell above the expected result
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "marie" wrote in message ... I got it. I had the cells formatted the same way, but I tested it further. Because the values on columns A and E had numbers that starts with '00', for some reason, excel is not recognizing the first 2 digits once the formatting has been changed to general from numeric. Thanks for the help. "Ragdyer" wrote: This usually denotes that your data is not equal in both lists. To test this, *manually* type in a value in Column E, and *manually* type in the *exact* same value in Column A, so that you *know* that you have a match. Also, make sure both test cells are the same format. What happens now? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "marie" wrote in message ... When I changed the 4th argument to FALSE or 0, my result shows N/A. I tried removing the 4th argument, but the result is the same as when I had the 4th arg of TRUE in it. "RagDyeR" wrote: What happens if you change "True" to "False" (no quotes)? -- Regards, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "marie" wrote in message ... Hi there, For some reason, my vlookup formula results in the match that appears a cell above the correct match. The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE) My cells are fomatted to 'General' (not text or number). I've tried sorting, but it did not fix the problem. There are 5 columns on my worksheet (1st column shows asset ID, 2nd column shows corresponding name of the asset, column 3 shows Available Units, column 4 shows the price). Column 5 (E) shows the asset ID's that I am finding a match for. I need to show (as a result) the Available Units once a match between ID's is found. Thanks for your help. Marie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The formula shows in cell but not the result | Excel Discussion (Misc queries) | |||
Cell shows formula eg ='Sheet21'!F3 instead of result | Excel Worksheet Functions | |||
Formula result as real empty/blank cell | Excel Worksheet Functions | |||
Sum formula result shows "#N/A" | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |