Linking Worksheets and Lookup

[braitman]

Folks:

Much appreciate any help out of this situation.

I have a worksheet that contains ten columns of data with 6,608 cells
in each column. Each cell is a mixed value of number + text (example:
03510.mp3).

In the same workbook I have another worksheet that contains columns
that provide the key to those mixed values. One column is the mixed
values (03510.mp3) and the adjacent column provides the definition in
text (example: Ray Bradbury).

What I want to do is have all 68,080 cells in the first worksheet look
up the key in the second worksheet and extract the key definition text.


I want the text definition added to my first worksheet (the one with
68,080 cells), so that I can identify each one of the values with its
text definition.

How do I do this?

I know enough to get in trouble because I've gone down the road of
trying to set up a formula that combines VLOOKUP with [WorkbookName]
linking. However, it's not working, so I must be doing something wrong.


Thanks for any illumination you can send my way!

Stephen

 

[JMB]

More on VLOOKUP here.
http://contextures.com/xlFunctions02.html

Are the codes and definitions all in one table (ie the codes in column A and
the definitions in column B). Unless you're using Office 2007, there are
only 65536 rows and you said you had 68080 codes to look up (unless there are
duplicates or you are using Office 2007-you may need two tables).

 

[TimS]

Hi. You should be able to use VLookup with something like the following:
=IF(ISNA(VLOOKUP(A1,'Sheet 2'!A$1:B$3,2,FALSE)),"Not
Found",VLOOKUP(A1,'Sheet 2'!A$1:B$3,2,FALSE))

I have included the ISNA function to ensure that you don't get the dreaded
#NA.

Hope this helps.

 

[braitman]

More on VLOOKUP here.
http://contextures.com/xlFunctions02.html

Are the codes and definitions all in one table (ie the codes in column A and
the definitions in column B). Unless you're using Office 2007, there are
only 65536 rows and you said you had 68080 codes to look up (unless there are
duplicates or you are using Office 2007-you may need two tables).

Hi, JMB, thanks for the reply:

Codes and definitions are all in one table (worksheet), with codes in A
and defs in B. There are 6,785 rows of the two columns (A & B).

On another table (worksheet), are 11 columns (A-K) of codes, with each
column having 6607 cells, i.e. total of 72,677 codes (in cells) that
need to have definitions (from other other table/worksheet) attached.

Sorry my math was off a bit the last time.

Stephen

 

[braitman]

Thanks for everyone's patience on this!

Here is a breakdown of the specific tasks that I need accomplished. I'm
uncertain now whether this is LOOKUP, VLOOKUP, MATCH, INDEX,
MATCH/INDEX or what.

1) Search Range A1:A6785 in Worksheet #1 for the matches in every cell
in Worksheet #2.

2) Find the corresponding adjacent text data in Range B1:B6785 in
Worksheet #1.

3) Copy the found text data from Range B1:B6785 in Worksheet #1

4) Paste found text date into new cells adjacent to matched cells in
Worksheet #2.

So, how do I do this?

Thanks!

Stephen

 

[JMB]

VLookup should work. I would insert some columns into sheet1 so that the
definitions will appear next to your codes. Column A is codes, column B is
empty, column C is codes, D is empty, and so on.

Then in B1,
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$6785, 2, 0)

Change Sheet2 to whatever Sheet2's actual name is. Then copy this formula
down column B. Then copy column B to column D, F, etc. If you get an error
for codes that you know exist, make sure the codes are actually exactly the
same (ie there are not any trailing spaces or special characters that look
like trailing spaces).