![]() |
VLookup Help
I have been doing vlookups for years and now I am having problems with it,
can someone please look at this formula and see if anything sticks out as being wrong? I am getting all #N/A's for results: =VLOOKUP(A2,'[Talaris Account history by cust part no. 200711-200810.xls]Account History'!$E$8:$E$496,1,FALSE) Thank you! |
VLookup Help
The #N/A error means that you are not getting an exact match. You
might think that there is a match, but it might be that in one file you have a proper number and in the other file you have a number which is actually a text value. Here are two ways of getting round this particular issue: =VLOOKUP(A2*1, ... etc or =VLOOKUP(A2&"", ... etc The first one converts a text number in A2 to a proper number, if you have proper numbers in your other file, whereas the second approach converts a number in A2 to a text value. One other point is that the formula implies that the other file is open, as you do not have the full-path before the filename. I'm not sure why you are using VLOOKUP - if you find a match then it will only return itself, as your table is only one column wide. Hope this helps. Pete On Oct 31, 7:22*pm, Sorceressss wrote: I have been doing vlookups for years and now I am having problems with it, can someone please look at this formula and see if anything sticks out as being wrong? *I am getting all #N/A's for results: =VLOOKUP(A2,'[Talaris Account history by cust part no. 200711-200810.xls]Account History'!$E$8:$E$496,1,FALSE) Thank you! |
VLookup Help
Hi,
The formula is fine so the NA# indicates no match for the data in A2. Usual culprits are rogue spaces. Mike "Sorceressss" wrote: I have been doing vlookups for years and now I am having problems with it, can someone please look at this formula and see if anything sticks out as being wrong? I am getting all #N/A's for results: =VLOOKUP(A2,'[Talaris Account history by cust part no. 200711-200810.xls]Account History'!$E$8:$E$496,1,FALSE) Thank you! |
VLookup Help
Thanks for the answers! I am using Vlookup to find common customer parts in
two different spreadsheets. Is there a better function to do this? "Pete_UK" wrote: The #N/A error means that you are not getting an exact match. You might think that there is a match, but it might be that in one file you have a proper number and in the other file you have a number which is actually a text value. Here are two ways of getting round this particular issue: =VLOOKUP(A2*1, ... etc or =VLOOKUP(A2&"", ... etc The first one converts a text number in A2 to a proper number, if you have proper numbers in your other file, whereas the second approach converts a number in A2 to a text value. One other point is that the formula implies that the other file is open, as you do not have the full-path before the filename. I'm not sure why you are using VLOOKUP - if you find a match then it will only return itself, as your table is only one column wide. Hope this helps. Pete On Oct 31, 7:22 pm, Sorceressss wrote: I have been doing vlookups for years and now I am having problems with it, can someone please look at this formula and see if anything sticks out as being wrong? I am getting all #N/A's for results: =VLOOKUP(A2,'[Talaris Account history by cust part no. 200711-200810.xls]Account History'!$E$8:$E$496,1,FALSE) Thank you! |
VLookup Help
You are right Mike! I hit F2 in a cell and there are a bunch of spaces after
the part number in one spreadsheet. How can I get rid of these? Thank you! "Mike H" wrote: Hi, The formula is fine so the NA# indicates no match for the data in A2. Usual culprits are rogue spaces. Mike "Sorceressss" wrote: I have been doing vlookups for years and now I am having problems with it, can someone please look at this formula and see if anything sticks out as being wrong? I am getting all #N/A's for results: =VLOOKUP(A2,'[Talaris Account history by cust part no. 200711-200810.xls]Account History'!$E$8:$E$496,1,FALSE) Thank you! |
VLookup Help
I used the TRIM Funtion, thank you gentlemen for your help!!!
"Mike H" wrote: Hi, The formula is fine so the NA# indicates no match for the data in A2. Usual culprits are rogue spaces. Mike "Sorceressss" wrote: I have been doing vlookups for years and now I am having problems with it, can someone please look at this formula and see if anything sticks out as being wrong? I am getting all #N/A's for results: =VLOOKUP(A2,'[Talaris Account history by cust part no. 200711-200810.xls]Account History'!$E$8:$E$496,1,FALSE) Thank you! |
VLookup Help
Well, you could use a MATCH function.
Pete On Oct 31, 7:46*pm, Sorceressss wrote: Thanks for the answers! *I am using Vlookup to find common customer parts in two different spreadsheets. *Is there a better function to do this? "Pete_UK" wrote: The #N/A error means that you are not getting an exact match. You might think that there is a match, but it might be that in one file you have a proper number and in the other file you have a number which is actually a text value. Here are two ways of getting round this particular issue: =VLOOKUP(A2*1, ... etc or =VLOOKUP(A2&"", ... etc The first one converts a text number in A2 to a proper number, if you have proper numbers in your other file, whereas the second approach converts a number in A2 to a text value. One other point is that the formula implies that the other file is open, as you do not have the full-path before the filename. I'm not sure why you are using VLOOKUP - if you find a match then it will only return itself, as your table is only one column wide. Hope this helps. Pete On Oct 31, 7:22 pm, Sorceressss wrote: I have been doing vlookups for years and now I am having problems with it, can someone please look at this formula and see if anything sticks out as being wrong? *I am getting all #N/A's for results: =VLOOKUP(A2,'[Talaris Account history by cust part no. 200711-200810.xls]Account History'!$E$8:$E$496,1,FALSE) Thank you!- Hide quoted text - - Show quoted text - |
All times are GMT +1. The time now is 12:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com