Hi,

I am working with quite a large amount of data, I have 2 workbooks, they
have one common column - ID, I am pulling information from workbook 1 (48K
records) into workbook 2 (110K records in csv) using a vlookup on the ID
column.

The formula is correct and works for all but about 3,500 records. At first I
thought that these records might not have a corresponding ID, but whilst
doing a couple of manual checks I found that the data was there and just
wasn't coming through. Also the records are grouped together, obviously I
can't check 3,500 records, but I have found over 20 consecutive rows that
aren't coming over into workbook 2, around about the 36,500 mark in book 1.

I have tried re-writing the Vlookup, narrowing the lookup criteria and it
still doesn't work. There seems to be some kind of problem reading the data
in the middle of the workbook.

Has anyone seen anything like this before? Any ideas?

You can check all the records with
=COUNTIF(H1:H20,"#N/A")
or with AutoFilter showing rows with #N/A. I am assuming your'e using FALSE
as the 4th arg in VLOOKUP.

Two causes for #N/A:
Forget to use absolute columns in the Table reference.
Entries in the left column are formatted as text instead of numbers or
visa versa.

"Becksicle" wrote:

Hi,

I am working with quite a large amount of data, I have 2 workbooks, they
have one common column - ID, I am pulling information from workbook 1 (48K
records) into workbook 2 (110K records in csv) using a vlookup on the ID
column.

The formula is correct and works for all but about 3,500 records. At first I
thought that these records might not have a corresponding ID, but whilst
doing a couple of manual checks I found that the data was there and just
wasn't coming through. Also the records are grouped together, obviously I
can't check 3,500 records, but I have found over 20 consecutive rows that
aren't coming over into workbook 2, around about the 36,500 mark in book 1.

I have tried re-writing the Vlookup, narrowing the lookup criteria and it
still doesn't work. There seems to be some kind of problem reading the data
in the middle of the workbook.

Has anyone seen anything like this before? Any ideas?

Thanks Evan, I don't think I explained it very well, try to let me clarify. I
have filtered on my rows showing #N/A and then manually looked up the ID in
the other workbook, using Ctrl F. This is what I meant by not being able to
check all 3,500 records which are #N/A, there are over 100,000 records in
total so only about 3% are having this problem.

The column is formatted to text as it should be.

This is the formula I have used

=VLOOKUP(D2,[Book1.xls]sheet1!$A$2:$D$48146,4)

The rows which return #N/A in book 2 are very random, but during the manual
check I noticed they seem to be grouped around the row 36,000 mark in book 1,
which has over 48,000 records, all included in the formula. There are only
3,500 coming up with #N/A.

Can you help me further?


"Evan Weiner" wrote:

You can check all the records with
=COUNTIF(H1:H20,"#N/A")
or with AutoFilter showing rows with #N/A. I am assuming your'e using FALSE
as the 4th arg in VLOOKUP.

Two causes for #N/A:
Forget to use absolute columns in the Table reference.
Entries in the left column are formatted as text instead of numbers or
visa versa.

"Becksicle" wrote:

Hi,

I am working with quite a large amount of data, I have 2 workbooks, they
have one common column - ID, I am pulling information from workbook 1 (48K
records) into workbook 2 (110K records in csv) using a vlookup on the ID
column.

The formula is correct and works for all but about 3,500 records. At first I
thought that these records might not have a corresponding ID, but whilst
doing a couple of manual checks I found that the data was there and just
wasn't coming through. Also the records are grouped together, obviously I
can't check 3,500 records, but I have found over 20 consecutive rows that
aren't coming over into workbook 2, around about the 36,500 mark in book 1.

I have tried re-writing the Vlookup, narrowing the lookup criteria and it
still doesn't work. There seems to be some kind of problem reading the data
in the middle of the workbook.

Has anyone seen anything like this before? Any ideas?

It's getting out of my league. I'd be inclined to test without the link. I
avoid links. Also the conjectures site (Google "vlookup #N/A") mentions HTML
characters as a source of the problem.

"Becksicle" wrote:

Thanks Evan, I don't think I explained it very well, try to let me clarify. I
have filtered on my rows showing #N/A and then manually looked up the ID in
the other workbook, using Ctrl F. This is what I meant by not being able to
check all 3,500 records which are #N/A, there are over 100,000 records in
total so only about 3% are having this problem.

The column is formatted to text as it should be.

This is the formula I have used

=VLOOKUP(D2,[Book1.xls]sheet1!$A$2:$D$48146,4)

The rows which return #N/A in book 2 are very random, but during the manual
check I noticed they seem to be grouped around the row 36,000 mark in book 1,
which has over 48,000 records, all included in the formula. There are only
3,500 coming up with #N/A.

Can you help me further?


"Evan Weiner" wrote:

You can check all the records with
=COUNTIF(H1:H20,"#N/A")
or with AutoFilter showing rows with #N/A. I am assuming your'e using FALSE
as the 4th arg in VLOOKUP.

Two causes for #N/A:
Forget to use absolute columns in the Table reference.
Entries in the left column are formatted as text instead of numbers or
visa versa.

"Becksicle" wrote:

Hi,

I am working with quite a large amount of data, I have 2 workbooks, they
have one common column - ID, I am pulling information from workbook 1 (48K
records) into workbook 2 (110K records in csv) using a vlookup on the ID
column.

The formula is correct and works for all but about 3,500 records. At first I
thought that these records might not have a corresponding ID, but whilst
doing a couple of manual checks I found that the data was there and just
wasn't coming through. Also the records are grouped together, obviously I
can't check 3,500 records, but I have found over 20 consecutive rows that
aren't coming over into workbook 2, around about the 36,500 mark in book 1.

I have tried re-writing the Vlookup, narrowing the lookup criteria and it
still doesn't work. There seems to be some kind of problem reading the data
in the middle of the workbook.

Has anyone seen anything like this before? Any ideas?

Check the range once again

"Evan Weiner" wrote:

It's getting out of my league. I'd be inclined to test without the link. I
avoid links. Also the conjectures site (Google "vlookup #N/A") mentions HTML
characters as a source of the problem.

"Becksicle" wrote:

Thanks Evan, I don't think I explained it very well, try to let me clarify. I
have filtered on my rows showing #N/A and then manually looked up the ID in
the other workbook, using Ctrl F. This is what I meant by not being able to
check all 3,500 records which are #N/A, there are over 100,000 records in
total so only about 3% are having this problem.

The column is formatted to text as it should be.

This is the formula I have used

=VLOOKUP(D2,[Book1.xls]sheet1!$A$2:$D$48146,4)

The rows which return #N/A in book 2 are very random, but during the manual
check I noticed they seem to be grouped around the row 36,000 mark in book 1,
which has over 48,000 records, all included in the formula. There are only
3,500 coming up with #N/A.

Can you help me further?


"Evan Weiner" wrote:

You can check all the records with
=COUNTIF(H1:H20,"#N/A")
or with AutoFilter showing rows with #N/A. I am assuming your'e using FALSE
as the 4th arg in VLOOKUP.

Two causes for #N/A:
Forget to use absolute columns in the Table reference.
Entries in the left column are formatted as text instead of numbers or
visa versa.

"Becksicle" wrote:

Hi,

I am working with quite a large amount of data, I have 2 workbooks, they
have one common column - ID, I am pulling information from workbook 1 (48K
records) into workbook 2 (110K records in csv) using a vlookup on the ID
column.

The formula is correct and works for all but about 3,500 records. At first I
thought that these records might not have a corresponding ID, but whilst
doing a couple of manual checks I found that the data was there and just
wasn't coming through. Also the records are grouped together, obviously I
can't check 3,500 records, but I have found over 20 consecutive rows that
aren't coming over into workbook 2, around about the 36,500 mark in book 1.

I have tried re-writing the Vlookup, narrowing the lookup criteria and it
still doesn't work. There seems to be some kind of problem reading the data
in the middle of the workbook.

Has anyone seen anything like this before? Any ideas?

I've checked the range, I've changed the range, I've even narrowed it so it
only includes 10 rows, but still get the #N/A.

I think it has to be something to do with the rows in sheet 1 as oppose to
the formula in sheet 2, but I can see anything obvious and was hoping someone
had experienced something similar and would come back with a quick and easy
fix. Looks like that's not going to happen ;-)

Thanks for your help anyway.

"Sanjay" wrote:

Check the range once again

"Evan Weiner" wrote:

It's getting out of my league. I'd be inclined to test without the link. I
avoid links. Also the conjectures site (Google "vlookup #N/A") mentions HTML
characters as a source of the problem.

"Becksicle" wrote:

Thanks Evan, I don't think I explained it very well, try to let me clarify. I
have filtered on my rows showing #N/A and then manually looked up the ID in
the other workbook, using Ctrl F. This is what I meant by not being able to
check all 3,500 records which are #N/A, there are over 100,000 records in
total so only about 3% are having this problem.

The column is formatted to text as it should be.

This is the formula I have used

=VLOOKUP(D2,[Book1.xls]sheet1!$A$2:$D$48146,4)

The rows which return #N/A in book 2 are very random, but during the manual
check I noticed they seem to be grouped around the row 36,000 mark in book 1,
which has over 48,000 records, all included in the formula. There are only
3,500 coming up with #N/A.

Can you help me further?


"Evan Weiner" wrote:

You can check all the records with
=COUNTIF(H1:H20,"#N/A")
or with AutoFilter showing rows with #N/A. I am assuming your'e using FALSE
as the 4th arg in VLOOKUP.

Two causes for #N/A:
Forget to use absolute columns in the Table reference.
Entries in the left column are formatted as text instead of numbers or
visa versa.

"Becksicle" wrote:

Hi,

I am working with quite a large amount of data, I have 2 workbooks, they
have one common column - ID, I am pulling information from workbook 1 (48K
records) into workbook 2 (110K records in csv) using a vlookup on the ID
column.

The formula is correct and works for all but about 3,500 records. At first I
thought that these records might not have a corresponding ID, but whilst
doing a couple of manual checks I found that the data was there and just
wasn't coming through. Also the records are grouped together, obviously I
can't check 3,500 records, but I have found over 20 consecutive rows that
aren't coming over into workbook 2, around about the 36,500 mark in book 1.

I have tried re-writing the Vlookup, narrowing the lookup criteria and it
still doesn't work. There seems to be some kind of problem reading the data
in the middle of the workbook.

Has anyone seen anything like this before? Any ideas?

this happend, if range is not fixed (eg. $454$), in this case formula skip
one cell every time when we drag the formula but in your example you have
fixed the range so it tough to identify why it is showing #NA wihout looking
at you workbook.


"Becksicle" wrote:

I've checked the range, I've changed the range, I've even narrowed it so it
only includes 10 rows, but still get the #N/A.

I think it has to be something to do with the rows in sheet 1 as oppose to
the formula in sheet 2, but I can see anything obvious and was hoping someone
had experienced something similar and would come back with a quick and easy
fix. Looks like that's not going to happen ;-)

Thanks for your help anyway.

"Sanjay" wrote:

Check the range once again

"Evan Weiner" wrote:

It's getting out of my league. I'd be inclined to test without the link. I
avoid links. Also the conjectures site (Google "vlookup #N/A") mentions HTML
characters as a source of the problem.

"Becksicle" wrote:

Thanks Evan, I don't think I explained it very well, try to let me clarify. I
have filtered on my rows showing #N/A and then manually looked up the ID in
the other workbook, using Ctrl F. This is what I meant by not being able to
check all 3,500 records which are #N/A, there are over 100,000 records in
total so only about 3% are having this problem.

The column is formatted to text as it should be.

This is the formula I have used

=VLOOKUP(D2,[Book1.xls]sheet1!$A$2:$D$48146,4)

The rows which return #N/A in book 2 are very random, but during the manual
check I noticed they seem to be grouped around the row 36,000 mark in book 1,
which has over 48,000 records, all included in the formula. There are only
3,500 coming up with #N/A.

Can you help me further?


"Evan Weiner" wrote:

You can check all the records with
=COUNTIF(H1:H20,"#N/A")
or with AutoFilter showing rows with #N/A. I am assuming your'e using FALSE
as the 4th arg in VLOOKUP.

Two causes for #N/A:
Forget to use absolute columns in the Table reference.
Entries in the left column are formatted as text instead of numbers or
visa versa.

"Becksicle" wrote:

Hi,

I am working with quite a large amount of data, I have 2 workbooks, they
have one common column - ID, I am pulling information from workbook 1 (48K
records) into workbook 2 (110K records in csv) using a vlookup on the ID
column.

The formula is correct and works for all but about 3,500 records. At first I
thought that these records might not have a corresponding ID, but whilst
doing a couple of manual checks I found that the data was there and just
wasn't coming through. Also the records are grouped together, obviously I
can't check 3,500 records, but I have found over 20 consecutive rows that
aren't coming over into workbook 2, around about the 36,500 mark in book 1.

I have tried re-writing the Vlookup, narrowing the lookup criteria and it
still doesn't work. There seems to be some kind of problem reading the data
in the middle of the workbook.

Has anyone seen anything like this before? Any ideas?

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Becksicle wrote:

Hi,

I am working with quite a large amount of data, I have 2 workbooks, they
have one common column - ID, I am pulling information from workbook 1 (48K
records) into workbook 2 (110K records in csv) using a vlookup on the ID
column.

The formula is correct and works for all but about 3,500 records. At first I
thought that these records might not have a corresponding ID, but whilst
doing a couple of manual checks I found that the data was there and just
wasn't coming through. Also the records are grouped together, obviously I
can't check 3,500 records, but I have found over 20 consecutive rows that
aren't coming over into workbook 2, around about the 36,500 mark in book 1.

I have tried re-writing the Vlookup, narrowing the lookup criteria and it
still doesn't work. There seems to be some kind of problem reading the data
in the middle of the workbook.

Has anyone seen anything like this before? Any ideas?

--

Dave Peterson