VLOOKUP in external worksheet - Syntax Error?

  • Thread starter Thread starter J.Love
  • Start date Start date
J

J.Love

First of all, if I use "worksheet" when I mean "workbook" or
vice-versa, just make the appropriate mental adjustment. Part of my
struggle is getting this terminology sorted out.

I have two XLS spreadsheets (workbooks?) and I'm trying to populate
the first one with a value lookup from the second one. Here's a brief
look at the relevant elements structure of the spreadsheets.

Workbook PROFILE

A ... E
3 (=Formula) ... 12346

External Workbook (C:\XL\Cust2.xls - worksheet named CUST)

.... G ... BX
2 ABC Company ... 12345
3 BCD Company ... 12346
4 EFG Company ... 12347

Desired result in PROFILE:
A ... E
3 BCD Company ... 12346

(Where "BCD Company" is the result of the formula looking up with
Profile cell E3 into CUST column BX, returning CUST column G's
corresponding entry.)

The formula I tried was:

=VLOOKUP(E3,'C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
or
=VLOOKUP(E3,''C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
with two single quote marks preceeding the external name as suggested
by another posting, but that only introduced a different error.

The result it gives is "#N/A" and I don't know why. Can I not look up
from worksheet PROFILE with E3 as the source data, to external
worksheet CUST in column BX and go left and get the corresponding row
result in G, transferring this over to cell A3? Or is VLOOKUP the
wrong formula for this? The error seems to point to E3 (it was
highlighted) but there's nothing wrong or unusual about that data. Is
the range G2:BX99 wrong? My values are actually in BX and G as
indicated.

There can be multiple/duplicate values in CUST cell BX, but the
company name is repeated if there is a second equal value, so the
first occurence would suffice. I did sort the data in CUST, using BX
as an ascending value as the example shows.

I've tried dozens of combinations of syntax with no better results.
I've read the help but all the terminology is very confusing.

Any suggestions would be VERY much appreciated.
 
The Lookup Value (in your case E3) must be in Column 1 of your Lookup Table,
for starters. Not sure but also, in the Lookup Table Column 1 must also be
in ascending order..(which you already seem to have going for you..)
HTH
 
slight correction... where I said:
(in your case E3) must be in Column 1 of your Lookup Table
I should have said:
(in your case E3) must be in Column 1 of your Lookup Table Range.
HTH

JMay said:
The Lookup Value (in your case E3) must be in Column 1 of your Lookup Table,
for starters. Not sure but also, in the Lookup Table Column 1 must also be
in ascending order..(which you already seem to have going for you..)
HTH

J.Love said:
First of all, if I use "worksheet" when I mean "workbook" or
vice-versa, just make the appropriate mental adjustment. Part of my
struggle is getting this terminology sorted out.

I have two XLS spreadsheets (workbooks?) and I'm trying to populate
the first one with a value lookup from the second one. Here's a brief
look at the relevant elements structure of the spreadsheets.

Workbook PROFILE

A ... E
3 (=Formula) ... 12346

External Workbook (C:\XL\Cust2.xls - worksheet named CUST)

... G ... BX
2 ABC Company ... 12345
3 BCD Company ... 12346
4 EFG Company ... 12347

Desired result in PROFILE:
A ... E
3 BCD Company ... 12346

(Where "BCD Company" is the result of the formula looking up with
Profile cell E3 into CUST column BX, returning CUST column G's
corresponding entry.)

The formula I tried was:

=VLOOKUP(E3,'C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
or
=VLOOKUP(E3,''C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
with two single quote marks preceeding the external name as suggested
by another posting, but that only introduced a different error.

The result it gives is "#N/A" and I don't know why. Can I not look up
from worksheet PROFILE with E3 as the source data, to external
worksheet CUST in column BX and go left and get the corresponding row
result in G, transferring this over to cell A3? Or is VLOOKUP the
wrong formula for this? The error seems to point to E3 (it was
highlighted) but there's nothing wrong or unusual about that data. Is
the range G2:BX99 wrong? My values are actually in BX and G as
indicated.

There can be multiple/duplicate values in CUST cell BX, but the
company name is repeated if there is a second equal value, so the
first occurence would suffice. I did sort the data in CUST, using BX
as an ascending value as the example shows.

I've tried dozens of combinations of syntax with no better results.
I've read the help but all the terminology is very confusing.

Any suggestions would be VERY much appreciated.
Click to expand...
Click to expand...
 
Random wrote:
<First of all, if I use "worksheet" when I mean "workbook" or
vice-versa, just make the appropriate mental adjustment.>

And what crystal ball do you expect the rest of the world to use to
determine which it is you mean?

Alan Beban
I believe you are trying to work backwards on the VLOOKUP Function
The value to be looked up must be in the leftmost column of the table
you are searching.

The Lookup Value (in your case E3) must be in Column 1 of your Lookup Table,
for starters. Not sure but also, in the Lookup Table Column 1 must also be
in ascending order..(which you already seem to have going for you..)
HTH

First of all, if I use "worksheet" when I mean "workbook" or
vice-versa, just make the appropriate mental adjustment. Part of my
struggle is getting this terminology sorted out.

I have two XLS spreadsheets (workbooks?) and I'm trying to populate
the first one with a value lookup from the second one. Here's a brief
look at the relevant elements structure of the spreadsheets.

Workbook PROFILE

A ... E
3 (=Formula) ... 12346

External Workbook (C:\XL\Cust2.xls - worksheet named CUST)

... G ... BX
2 ABC Company ... 12345
3 BCD Company ... 12346
4 EFG Company ... 12347

Desired result in PROFILE:
A ... E
3 BCD Company ... 12346

(Where "BCD Company" is the result of the formula looking up with
Profile cell E3 into CUST column BX, returning CUST column G's
corresponding entry.)

The formula I tried was:

=VLOOKUP(E3,'C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
or
=VLOOKUP(E3,''C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
with two single quote marks preceeding the external name as suggested
by another posting, but that only introduced a different error.

The result it gives is "#N/A" and I don't know why. Can I not look up
from worksheet PROFILE with E3 as the source data, to external
worksheet CUST in column BX and go left and get the corresponding row
result in G, transferring this over to cell A3? Or is VLOOKUP the
wrong formula for this? The error seems to point to E3 (it was
highlighted) but there's nothing wrong or unusual about that data. Is
the range G2:BX99 wrong? My values are actually in BX and G as
indicated.

There can be multiple/duplicate values in CUST cell BX, but the
company name is repeated if there is a second equal value, so the
first occurence would suffice. I did sort the data in CUST, using BX
as an ascending value as the example shows.

I've tried dozens of combinations of syntax with no better results.
I've read the help but all the terminology is very confusing.

Any suggestions would be VERY much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, Random

This was intended to be a comment on the statement quoted below, which
was the original poster's statement, not yours.

My apologies,
Alan Beban

Alan said:
Random wrote:
<First of all, if I use "worksheet" when I mean "workbook" or
vice-versa, just make the appropriate mental adjustment.>

And what crystal ball do you expect the rest of the world to use to
determine which it is you mean?

Alan Beban
I believe you are trying to work backwards on the VLOOKUP Function
The value to be looked up must be in the leftmost column of the table
you are searching.

The Lookup Value (in your case E3) must be in Column 1 of your Lookup
Table,
for starters. Not sure but also, in the Lookup Table Column 1 must
also be
in ascending order..(which you already seem to have going for you..)
HTH


First of all, if I use "worksheet" when I mean "workbook" or
vice-versa, just make the appropriate mental adjustment. Part of my
struggle is getting this terminology sorted out.

I have two XLS spreadsheets (workbooks?) and I'm trying to populate
the first one with a value lookup from the second one. Here's a brief
look at the relevant elements structure of the spreadsheets.

Workbook PROFILE

A ... E
3 (=Formula) ... 12346

External Workbook (C:\XL\Cust2.xls - worksheet named CUST)

... G ... BX
2 ABC Company ... 12345
3 BCD Company ... 12346
4 EFG Company ... 12347

Desired result in PROFILE:
A ... E
3 BCD Company ... 12346

(Where "BCD Company" is the result of the formula looking up with
Profile cell E3 into CUST column BX, returning CUST column G's
corresponding entry.)

The formula I tried was:

=VLOOKUP(E3,'C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
or
=VLOOKUP(E3,''C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
with two single quote marks preceeding the external name as suggested
by another posting, but that only introduced a different error.

The result it gives is "#N/A" and I don't know why. Can I not look up
from worksheet PROFILE with E3 as the source data, to external
worksheet CUST in column BX and go left and get the corresponding row
result in G, transferring this over to cell A3? Or is VLOOKUP the
wrong formula for this? The error seems to point to E3 (it was
highlighted) but there's nothing wrong or unusual about that data. Is
the range G2:BX99 wrong? My values are actually in BX and G as
indicated.

There can be multiple/duplicate values in CUST cell BX, but the
company name is repeated if there is a second equal value, so the
first occurence would suffice. I did sort the data in CUST, using BX
as an ascending value as the example shows.

I've tried dozens of combinations of syntax with no better results.
I've read the help but all the terminology is very confusing.

Any suggestions would be VERY much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
That's fine, and I did copy the column over so it could
work forwards, the way VLOOKUP is supposed to work.

However, I still get the same result. Is my formula syntax
correct?

Could it be formatting of the cell that's tripping me up?
Looking up value "123" if the reference value is "000123"?

Thanks.
-----Original Message-----
I believe you are trying to work backwards on the VLOOKUP Function
The value to be looked up must be in the leftmost column of the table
you are searching.

The Lookup Value (in your case E3) must be in Column 1 of your Lookup Table,
for starters. Not sure but also, in the Lookup Table Column 1 must also be
in ascending order..(which you already seem to have going for you..)
HTH

First of all, if I use "worksheet" when I mean "workbook" or
vice-versa, just make the appropriate mental adjustment. Part of my
struggle is getting this terminology sorted out.

I have two XLS spreadsheets (workbooks?) and I'm trying to populate
the first one with a value lookup from the second one. Here's a brief
look at the relevant elements structure of the spreadsheets.

Workbook PROFILE

A ... E
3 (=Formula) ... 12346

External Workbook (C:\XL\Cust2.xls - worksheet named CUST)

... G ... BX
2 ABC Company ... 12345
3 BCD Company ... 12346
4 EFG Company ... 12347

Desired result in PROFILE:
A ... E
3 BCD Company ... 12346

(Where "BCD Company" is the result of the formula looking up with
Profile cell E3 into CUST column BX, returning CUST column G's
corresponding entry.)

The formula I tried was:

=VLOOKUP(E3,'C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
or
=VLOOKUP(E3,''C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
with two single quote marks preceeding the external name as suggested
by another posting, but that only introduced a different error.

The result it gives is "#N/A" and I don't know why. Can I not look up
from worksheet PROFILE with E3 as the source data, to external
worksheet CUST in column BX and go left and get the corresponding row
result in G, transferring this over to cell A3? Or is VLOOKUP the
wrong formula for this? The error seems to point to E3 (it was
highlighted) but there's nothing wrong or unusual about that data. Is
the range G2:BX99 wrong? My values are actually in BX and G as
indicated.

There can be multiple/duplicate values in CUST cell BX, but the
company name is repeated if there is a second equal value, so the
first occurence would suffice. I did sort the data in CUST, using BX
as an ascending value as the example shows.

I've tried dozens of combinations of syntax with no better results.
I've read the help but all the terminology is very confusing.

Any suggestions would be VERY much appreciated.
Click to expand...
Click to expand...

.
Click to expand...
Click to expand...
 
That looks like you've isolated the problem. I don't think it is
formatting though. I thing the 000123 is saved as text. Change the
column on your profile page to text and reenter the value of 000123.
It should appear as 000123 instead of 123. The lookup should work
after that.



That's fine, and I did copy the column over so it could
work forwards, the way VLOOKUP is supposed to work.

However, I still get the same result. Is my formula syntax
correct?

Could it be formatting of the cell that's tripping me up?
Looking up value "123" if the reference value is "000123"?

Thanks.
-----Original Message-----
I believe you are trying to work backwards on the VLOOKUP Function
The value to be looked up must be in the leftmost column of the table
you are searching.

The Lookup Value (in your case E3) must be in Column 1 of your Lookup Table,
for starters. Not sure but also, in the Lookup Table Column 1 must also be
in ascending order..(which you already seem to have going for you..)
HTH

First of all, if I use "worksheet" when I mean "workbook" or
vice-versa, just make the appropriate mental adjustment. Part of my
struggle is getting this terminology sorted out.

I have two XLS spreadsheets (workbooks?) and I'm trying to populate
the first one with a value lookup from the second one. Here's a brief
look at the relevant elements structure of the spreadsheets.

Workbook PROFILE

A ... E
3 (=Formula) ... 12346

External Workbook (C:\XL\Cust2.xls - worksheet named CUST)

... G ... BX
2 ABC Company ... 12345
3 BCD Company ... 12346
4 EFG Company ... 12347

Desired result in PROFILE:
A ... E
3 BCD Company ... 12346

(Where "BCD Company" is the result of the formula looking up with
Profile cell E3 into CUST column BX, returning CUST column G's
corresponding entry.)

The formula I tried was:

=VLOOKUP(E3,'C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
or
=VLOOKUP(E3,''C:\XL\[Cust2.xls]Cust'!G2:BX99,1,FALSE)
with two single quote marks preceeding the external name as suggested
by another posting, but that only introduced a different error.

The result it gives is "#N/A" and I don't know why. Can I not look up
from worksheet PROFILE with E3 as the source data, to external
worksheet CUST in column BX and go left and get the corresponding row
result in G, transferring this over to cell A3? Or is VLOOKUP the
wrong formula for this? The error seems to point to E3 (it was
highlighted) but there's nothing wrong or unusual about that data. Is
the range G2:BX99 wrong? My values are actually in BX and G as
indicated.

There can be multiple/duplicate values in CUST cell BX, but the
company name is repeated if there is a second equal value, so the
first occurence would suffice. I did sort the data in CUST, using BX
as an ascending value as the example shows.

I've tried dozens of combinations of syntax with no better results.
I've read the help but all the terminology is very confusing.

Any suggestions would be VERY much appreciated.
Click to expand...

.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top