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?

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.

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?

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.

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.