ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   VLookup Help (https://www.excelbanter.com/excel-discussion-misc-queries/208565-vlookup-help.html)

Sorceressss

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!

Pete_UK

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!



Mike H

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!


Sorceressss

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!




Sorceressss

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!


Sorceressss

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!


Pete_UK

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