Vlookup to an external file
 

I have a Vlookup in my workbook looing at a table in another workbook. It
works fine until what I'm looking for is more than 10082 lines down the
table, formula I'm using is:

=VLOOKUP($C3,[GBP_USER]Sheet1!$A$3:$I$65536,8,FALSE)

Does anyone know of a work around?

Vlookup to an external file
 

Hi,
what do you mean that you have problems when is more than 10082 lines, do
you get an error message ???

"Lee Crew" wrote:

I have a Vlookup in my workbook looing at a table in another workbook. It
works fine until what I'm looking for is more than 10082 lines down the
table, formula I'm using is:

=VLOOKUP($C3,[GBP_USER]Sheet1!$A$3:$I$65536,8,FALSE)

Does anyone know of a work around?

Vlookup to an external file
 

No Error message just get back "#N/A"

In my look up table it has over 25,000 lines, when looking for a word in the
table it returns correct information for the correct word expect when
searching the lookup table for a word that is more than 10082 lines down the
list the above is returned

"Eduardo" wrote:

Hi,
what do you mean that you have problems when is more than 10082 lines, do
you get an error message ???

"Lee Crew" wrote:

I have a Vlookup in my workbook looing at a table in another workbook. It
works fine until what I'm looking for is more than 10082 lines down the
table, formula I'm using is:

=VLOOKUP($C3,[GBP_USER]Sheet1!$A$3:$I$65536,8,FALSE)

Does anyone know of a work around?

Vlookup to an external file
 

Hi Lee,
that error is because the name doesn't exist in the table, it might be a
blank space in one of the names, to check that you can copy the name you are
searching on top of the other name in the table you are applying the Vlookup,
or where you have your table, use a helper column with this formula

=trim(A1)

then copy the results as values on top or your names, that will delete all
blank spaces

"Lee Crew" wrote:

No Error message just get back "#N/A"

In my look up table it has over 25,000 lines, when looking for a word in the
table it returns correct information for the correct word expect when
searching the lookup table for a word that is more than 10082 lines down the
list the above is returned

"Eduardo" wrote:

Hi,
what do you mean that you have problems when is more than 10082 lines, do
you get an error message ???

"Lee Crew" wrote:

I have a Vlookup in my workbook looing at a table in another workbook. It
works fine until what I'm looking for is more than 10082 lines down the
table, formula I'm using is:

=VLOOKUP($C3,[GBP_USER]Sheet1!$A$3:$I$65536,8,FALSE)

Does anyone know of a work around?

Vlookup to an external file
 

#N/A when used with the fourth parameter of FALSE means there was no
exact match found. If you believe there is an exact match (at row
10082 perhaps?) then you need to examine that value more closely. You
may have leading or trailing spaces (which you can't see) so you need
to try a formula like =LEN(A10082) to check for things like this.

Hope this helps.

Pete

On Sep 21, 3:19*pm, Lee Crew
wrote:
No Error message just get back "#N/A"

In my look up table it has over 25,000 lines, when looking for a word in the
table it returns correct information for the correct word expect when
searching the lookup table for a word that is more than 10082 lines down the
list the above is returned



"Eduardo" wrote:
Hi,
what do you mean that you have problems when is more than 10082 lines, do
you get an error message ???

"Lee Crew" wrote:

I have a Vlookup in my workbook looing at a table in another workbook.. It
works fine until what I'm looking for is more than 10082 lines down the
table, formula I'm using is:

=VLOOKUP($C3,[GBP_USER]Sheet1!$A$3:$I$65536,8,FALSE)

Does anyone know of a work around?- Hide quoted text -

- Show quoted text -

Vlookup to an external file
 

Hi Eduardo

It definatley exists, i've copied a word out from the table and entred is
into cell C3 as a test but get the same error


Thanks Lee

"Eduardo" wrote:

Hi Lee,
that error is because the name doesn't exist in the table, it might be a
blank space in one of the names, to check that you can copy the name you are
searching on top of the other name in the table you are applying the Vlookup,
or where you have your table, use a helper column with this formula

=trim(A1)

then copy the results as values on top or your names, that will delete all
blank spaces

"Lee Crew" wrote:

No Error message just get back "#N/A"

In my look up table it has over 25,000 lines, when looking for a word in the
table it returns correct information for the correct word expect when
searching the lookup table for a word that is more than 10082 lines down the
list the above is returned

"Eduardo" wrote:

Hi,
what do you mean that you have problems when is more than 10082 lines, do
you get an error message ???

"Lee Crew" wrote:

I have a Vlookup in my workbook looing at a table in another workbook. It
works fine until what I'm looking for is more than 10082 lines down the
table, formula I'm using is:

=VLOOKUP($C3,[GBP_USER]Sheet1!$A$3:$I$65536,8,FALSE)

Does anyone know of a work around?

Vlookup to an external file
 

If you're absolutely positive that it exists, then try this test.

Find a cell that returns an error (say C99).

Then look at the other worksheet and find that value that you know matches (say
A999).

Now find an empty cell in the first worksheet.

Put this formula:
=c3=[GBP_USER.xls]Sheet1!a999

If it returns True, then that means that there is a match and everyone is wrong
(including me!).

If it returns False, then there is a difference that you're not noticing.

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

If this doesn't help, you may want to share some of the data (and types of data)
that you're matching on--is it a number or text, a long string, date or time or
combination???



Lee Crew wrote:

Hi Eduardo

It definatley exists, i've copied a word out from the table and entred is
into cell C3 as a test but get the same error

Thanks Lee

"Eduardo" wrote:

Hi Lee,
that error is because the name doesn't exist in the table, it might be a
blank space in one of the names, to check that you can copy the name you are
searching on top of the other name in the table you are applying the Vlookup,
or where you have your table, use a helper column with this formula

=trim(A1)

then copy the results as values on top or your names, that will delete all
blank spaces

"Lee Crew" wrote:

No Error message just get back "#N/A"

In my look up table it has over 25,000 lines, when looking for a word in the
table it returns correct information for the correct word expect when
searching the lookup table for a word that is more than 10082 lines down the
list the above is returned

"Eduardo" wrote:

Hi,
what do you mean that you have problems when is more than 10082 lines, do
you get an error message ???

"Lee Crew" wrote:

I have a Vlookup in my workbook looing at a table in another workbook. It
works fine until what I'm looking for is more than 10082 lines down the
table, formula I'm using is:

=VLOOKUP($C3,[GBP_USER]Sheet1!$A$3:$I$65536,8,FALSE)

Does anyone know of a work around?

--

Dave Peterson

Vlookup to an external file
 

On Mon, 21 Sep 2009 07:19:02 -0700, Lee Crew
wrote:

No Error message just get back "#N/A"

In my look up table it has over 25,000 lines, when looking for a word in the
table it returns correct information for the correct word expect when
searching the lookup table for a word that is more than 10082 lines down the
list the above is returned

I tried to recreate this, but as far as I can see #N/A just means that the
lookup value can't be found.

I got over 19000 rows without a problem, that's where I stopped increasing
my table.
My lookup table contained two columns, numbers 1-19350 in the first column
and the bahttext() function of each number in the second.

Vlookup to an external file
 

Use the offending cells:
=c3=[GBP_USER.xls]Sheet1!a999
should have been
=c99=[GBP_USER.xls]Sheet1!a999
(in my example)

Dave Peterson wrote:

If you're absolutely positive that it exists, then try this test.

Find a cell that returns an error (say C99).

Then look at the other worksheet and find that value that you know matches (say
A999).

Now find an empty cell in the first worksheet.

Put this formula:
=c3=[GBP_USER.xls]Sheet1!a999

If it returns True, then that means that there is a match and everyone is wrong
(including me!).

If it returns False, then there is a difference that you're not noticing.

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

If this doesn't help, you may want to share some of the data (and types of data)
that you're matching on--is it a number or text, a long string, date or time or
combination???

Lee Crew wrote:

Hi Eduardo

It definatley exists, i've copied a word out from the table and entred is
into cell C3 as a test but get the same error

Thanks Lee

"Eduardo" wrote:

Hi Lee,
that error is because the name doesn't exist in the table, it might be a
blank space in one of the names, to check that you can copy the name you are
searching on top of the other name in the table you are applying the Vlookup,
or where you have your table, use a helper column with this formula

=trim(A1)

then copy the results as values on top or your names, that will delete all
blank spaces

"Lee Crew" wrote:

No Error message just get back "#N/A"

In my look up table it has over 25,000 lines, when looking for a word in the
table it returns correct information for the correct word expect when
searching the lookup table for a word that is more than 10082 lines down the
list the above is returned

"Eduardo" wrote:

Hi,
what do you mean that you have problems when is more than 10082 lines, do
you get an error message ???

"Lee Crew" wrote:

I have a Vlookup in my workbook looing at a table in another workbook. It
works fine until what I'm looking for is more than 10082 lines down the
table, formula I'm using is:

=VLOOKUP($C3,[GBP_USER]Sheet1!$A$3:$I$65536,8,FALSE)

Does anyone know of a work around?

--

Dave Peterson

--

Dave Peterson