Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Worksheets and Lookup
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Worksheets and Lookup
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). "braitman" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Worksheets and Lookup
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Worksheets and Lookup
JMB wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Worksheets and Lookup (or MATCH/INDEX?)
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Worksheets and Lookup
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). "braitman" wrote: JMB wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup function for multiple worksheets...and then 'some' | Excel Worksheet Functions | |||
Master worksheet linking to multiple worksheets and using filter | Excel Discussion (Misc queries) | |||
Linking between multiple worksheets, workbooks and columns | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
need check two worksheets to lookup a value | Excel Discussion (Misc queries) |