Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup data across worksheets, one with 56K rows
I tried the Lookup Wizard to find information contained in one worksheet of
58,125 rows. I first tried with my source criteria in one worksheet of 147 rows and the info I needed in the bigger worksheet. The wizard didn't like that. I then copied the lookup values column of 147 rows into the bigger worksheet as a single column. The wizard returned a Visual Basic "Runtime error '6' - Overflow" error. The help button opened a blank window. What I'm trying to accomplish is to match data from one source we get daily to a master list so we can get the purchased licenses mailed out. The common info in the master worksheet and the daily sales worksheet is the NIN number. I want to lookup the licensee's information from the master worksheet using the NIN from the daily sales worksheet to then populate the respective fields in the daily sales worksheet. NIN = 1234567 Lookup returns: "Name", "address", "city", "state", "zip", etc. from master and inserts the data into the row for NIN 1234567 in the daily sales worksheet. clear as stained glass? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup data across worksheets, one with 56K rows
AlfNeuman wrote:
I tried the Lookup Wizard to find information contained in one worksheet of 58,125 rows. I first tried with my source criteria in one worksheet of 147 rows and the info I needed in the bigger worksheet. The wizard didn't like that. What do you mean "the wizard didn't like that"? Describe exactly the results you got. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup data across worksheets, one with 56K rows
Hi,
As described you can use the VLOOKUP function: =VLOOKUP(NIN,Sheet2!$A$1:$N$55000,2,FALSE) In this example NIN just means a cell containing an NIN #, say cell A1 of Sheet1, Sheet2!$A$1:$N$55000 represents the large data set with NIN #'s in column A and "Name" in column B. You will need a separate VLOOKUP for each item of info you want returned, so in the second formula you would change 2 to 3 to indicate that the Address was in column C or the 3rd column of the lookup range. -- If this helps, please click the Yes button Cheers, Shane Devenshire "AlfNeuman" wrote: I tried the Lookup Wizard to find information contained in one worksheet of 58,125 rows. I first tried with my source criteria in one worksheet of 147 rows and the info I needed in the bigger worksheet. The wizard didn't like that. I then copied the lookup values column of 147 rows into the bigger worksheet as a single column. The wizard returned a Visual Basic "Runtime error '6' - Overflow" error. The help button opened a blank window. What I'm trying to accomplish is to match data from one source we get daily to a master list so we can get the purchased licenses mailed out. The common info in the master worksheet and the daily sales worksheet is the NIN number. I want to lookup the licensee's information from the master worksheet using the NIN from the daily sales worksheet to then populate the respective fields in the daily sales worksheet. NIN = 1234567 Lookup returns: "Name", "address", "city", "state", "zip", etc. from master and inserts the data into the row for NIN 1234567 in the daily sales worksheet. clear as stained glass? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup data across worksheets, one with 56K rows
The Wizard apparently only works with a single worksheet or spreadsheet file.
I couldn't select one set of data from the daily sales worksheet and use that info to lookup the corresponding data in the master spreadsheet / worksheet. "Glenn" wrote: AlfNeuman wrote: I tried the Lookup Wizard to find information contained in one worksheet of 58,125 rows. I first tried with my source criteria in one worksheet of 147 rows and the info I needed in the bigger worksheet. The wizard didn't like that. What do you mean "the wizard didn't like that"? Describe exactly the results you got. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup data across worksheets, one with 56K rows
Just replace the static value the Wizard inserts into the formula with the
appropriate cell references. =INDEX(Source!$A$1:$Z$58000, MATCH("1234567",Source!$A$1:$A$58000,), MATCH("Name",Source!$A$1:$Z$1,)) becomes =INDEX(Source!$A$1:$Z$58000, MATCH($A2,Source!$A$1:$A$58000,), MATCH(B$1,Source!$A$1:$Z$1,)) AlfNeuman wrote: The Wizard apparently only works with a single worksheet or spreadsheet file. I couldn't select one set of data from the daily sales worksheet and use that info to lookup the corresponding data in the master spreadsheet / worksheet. "Glenn" wrote: What do you mean "the wizard didn't like that"? Describe exactly the results you got. AlfNeuman wrote: I tried the Lookup Wizard to find information contained in one worksheet of 58,125 rows. I first tried with my source criteria in one worksheet of 147 rows and the info I needed in the bigger worksheet. The wizard didn't like that. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Data Across Multiple Worksheets | Excel Discussion (Misc queries) | |||
Linking data from Rows to columns in seperate worksheets | Excel Worksheet Functions | |||
Combining data from worksheets - lookup? | Excel Discussion (Misc queries) | |||
sharing rows of data across multiple worksheets within a workbook | Excel Worksheet Functions | |||
Lookup values in a list and return multiple rows of data | Excel Worksheet Functions |