vlookup limitations?
 

I need help with an Excel spreadsheet. I have a workbook with two sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer (column
1) into 4 cells on the first sheet depending upon which customer is entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

vlookup limitations?
 

I have VLOOKUP working on full columns; 65535 rows in pre-2007 Excel.

160 items should not be a problem itself.
--
Gary''s Student - gsnu200761


"Larry" wrote:

I need help with an Excel spreadsheet. I have a workbook with two sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer (column
1) into 4 cells on the first sheet depending upon which customer is entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

vlookup limitations?
 

Perhaps you could post your actual formula used?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Larry" wrote in message
...
I need help with an Excel spreadsheet. I have a workbook with two sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer
(column
1) into 4 cells on the first sheet depending upon which customer is
entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

vlookup limitations?
 

Thanks for the info. I guess that there is something wrong with my syntax!
Got any suggestions?

"Gary''s Student" wrote:

I have VLOOKUP working on full columns; 65535 rows in pre-2007 Excel.

160 items should not be a problem itself.
--
Gary''s Student - gsnu200761


"Larry" wrote:

I need help with an Excel spreadsheet. I have a workbook with two sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer (column
1) into 4 cells on the first sheet depending upon which customer is entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

vlookup limitations?
 

What is your syntax? Post the formula you use!

"Larry" wrote in message
...
Thanks for the info. I guess that there is something wrong with my
syntax!
Got any suggestions?

"Gary''s Student" wrote:

I have VLOOKUP working on full columns; 65535 rows in pre-2007 Excel.

160 items should not be a problem itself.
--
Gary''s Student - gsnu200761


"Larry" wrote:

I need help with an Excel spreadsheet. I have a workbook with two
sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer
(column
1) into 4 cells on the first sheet depending upon which customer is
entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

vlookup limitations?
 

Currently I am only pulling in the Street information (column2). The cell on
sheet one where the customer is entered is I8. Formula is:
vlookup(I8,customers,2,false). Customers is the name of the second sheet. If
I choose a customer on a row below 160 it does not return any street. If I
change the false to true I get the address of the customer on row 160.
Thanks

"Max" wrote:

Perhaps you could post your actual formula used?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Larry" wrote in message
...
I need help with an Excel spreadsheet. I have a workbook with two sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer
(column
1) into 4 cells on the first sheet depending upon which customer is
entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

vlookup limitations?
 

In Sheet1,

Think you could try something like this in say, J8:
=VLOOKUP(I8,customers!A:B,2,FALSE)

Here, it's correct to use FALSE to look for an exact match.
Believe the prob was in your table array, this part: customers!A:B
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Larry" wrote:
Currently I am only pulling in the Street information (column2). The cell on
sheet one where the customer is entered is I8. Formula is:
vlookup(I8,customers,2,false). Customers is the name of the second sheet. If
I choose a customer on a row below 160 it does not return any street. If I
change the false to true I get the address of the customer on row 160.
Thanks

vlookup limitations?
 

Is "customers" a named range on the worksheet containing your data? Possibly
it is defined only as far as row 160? Look at the definition in
Insert Name Define
and adjust if necessary.


"Larry" wrote in message
...
Currently I am only pulling in the Street information (column2). The cell
on
sheet one where the customer is entered is I8. Formula is:
vlookup(I8,customers,2,false). Customers is the name of the second sheet.
If
I choose a customer on a row below 160 it does not return any street. If
I
change the false to true I get the address of the customer on row 160.
Thanks

"Max" wrote:

Perhaps you could post your actual formula used?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Larry" wrote in message
...
I need help with an Excel spreadsheet. I have a workbook with two
sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer
(column
1) into 4 cells on the first sheet depending upon which customer is
entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

vlookup limitations?
 

Maybe you defined Customers incorrectly.

What does it show in the "refers to" box (under the Insert|Name|Define dialog)

Larry wrote:

Currently I am only pulling in the Street information (column2). The cell on
sheet one where the customer is entered is I8. Formula is:
vlookup(I8,customers,2,false). Customers is the name of the second sheet. If
I choose a customer on a row below 160 it does not return any street. If I
change the false to true I get the address of the customer on row 160.
Thanks

"Max" wrote:

Perhaps you could post your actual formula used?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Larry" wrote in message
...
I need help with an Excel spreadsheet. I have a workbook with two sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer
(column
1) into 4 cells on the first sheet depending upon which customer is
entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?




--

Dave Peterson

vlookup limitations?
 

Thanks to both of you, both of you are right. Thanks again.

"Larry" wrote:

I need help with an Excel spreadsheet. I have a workbook with two sheets.
Second sheet has 5 columns and 800 rows. Columns a
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer (column
1) into 4 cells on the first sheet depending upon which customer is entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

vlookup limitations?
 

.. vlookup(I8,customers,2,false).
Customers is the name of the second sheet.

Admittedly, I fell for your 2nd line above in your earlier response, and
thought you might have crafted the table array wrongly. Afterall, you
re-typed the formula in your response (with possible typos, omissions, etc)
instead of just copying n pasting direct from the formula bar

If you had put the 2nd line as:
Customers is the name *on* the second sheet.
then I would have read "Customers" as a defined range
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---