| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Size/memory limitations for vlookup fixed in the new Excel? | Excel Discussion (Misc queries) | |||
| Limitations in MS Query? | Excel Discussion (Misc queries) | |||
| Excel Limitations | Excel Discussion (Misc queries) | |||
| VLOOKUP Limitations | Excel Worksheet Functions | |||
| Vlookup limitations | Excel Worksheet Functions | |||
![Larry[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
