Vlookup to an external file

[Lee Crew]

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?

 

[Eduardo]

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

 

[Lee Crew]

sorry yes there is a .xls

I've also checked and there are no spaces after or before the column I
looking up in the lookup table.

All formatting is the same

Thanks Lee

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?

That should not be a problem... are you sure the match you think it
should match up against is exact... I.e. no extra spaces at the end of
the word in column A or in C3. And that if you are looking up numbers,
that they are the same format below ro 10082...?

Also, should there be a .xls at the end of GBP_USER?


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)

 

[Lee Crew]

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

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

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?

 

[Eduardo]

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

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

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

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?

 

[Pete_UK]

#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

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

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

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 -

 

[Lee Crew]

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

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

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

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

:

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?

 

[Lee Crew]

I'm looking up diffent words in Column A, even if a manually type a word is
row 10082 and Type the same word in C3 the look up works, but if a do this
with A10083 it just comes back #N/A

Also not sure if this helps but for information but there are no duplicate
words in column A

Thanks
Lee

 

[Dave Peterson]

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???

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

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

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

:

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

:

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?

 

[Luc]

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.

 

[Dave Peterson]

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

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???

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

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

:

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

:

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

:

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?