lookup data across worksheets, one with 56K rows

[AlfNeuman]

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?

 

[Glenn]

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.

 

[Shane Devenshire]

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.

 

[AlfNeuman]

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]

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,))