1

I'm running Excel 2013 and I have two spreadsheets open. The first, sheet1, has two columns and 7k rows the columns are:

A                      B

ID                Description

58749651         a bunch of text

The second, sheet2, has 24 columns and 26,000 rows. Column "A" is the same as in the first sheet - title "ID" and the values are numbers. There is no "description" column in sheet2 - which is my question.

How can I create a description column on sheet 2 (it would be column "U") and populate it with the description from sheet 1 where the ID values match?

There's probably 1500 or so ID's on sheet1 that are also on sheet2 and I need to merge just the description from sheet1 into the appropriate row on sheet2.

Anyone have any ideas on how to do this or where I can find the information? Thanks! Rob

  • Hi Rob! Welcome to Super User. Are both the sheets (sheet1 and sheet2) in the same excel file (also called workbook)? – Prasanna Jan 26 '15 at 2:17
  • Hi Prasanna, thanks! No, they are two separate workbooks. – Rob50 Jan 26 '15 at 2:38
0

This would seem to be a job for VLOOKUP

U1:  =IFERROR(VLOOKUP(A1,[Book2]Sheet1!$A$1:$B$7000,2,FALSE),"")

Change Book2 to reflect the proper workbook name where your first list is stored.

  • Hey Ron, the first list, the one with just ID & description fields, is "apps1", the second workbook is "nodesc" …I tried changing "book2" to apps1 and it asked me to save the file? In which workbook should I put the formula - and does "sheet1" need to be renamed? Thanks! – Rob50 Jan 26 '15 at 3:42
  • The U1: at the beginning of the formula is the cell address where you should put the formula that refers to A1, obviously on the same worksheet since A1 is not qualified by workbook or worksheet name. Since you are having problems getting the workbook and worksheet references correct, I would suggest using the Insert Function bar and selecting the relevant cells in the relevant workbooks/sheets. Excel will create the proper references for you. – Ron Rosenfeld Jan 26 '15 at 4:05
  • Thanks again Ron…again, I know I'm missing something…when I put the formula in U1, in the "nodesc" sheet, change "book2" to "apps1", and hit enter…a dialog box opens titled "Update Values:apps1"…and, when I select "apps1" in the file name field I get an Excel error: "Excel cannot update one or more links in this workbook. To update the links, open all the link source files (click edit links on the data tab). So I get that error but also "apps1" is not the file I want to "update" - I want to update the "nodesc" file by inputting the description column from apps1. – Rob50 Jan 26 '15 at 16:55
  • Okay, I'm getting closer! I switched "book2" with "sheet1" and I the formula placed the column ID in U1…! But when I try to drag the formula down the length of column it just keeps putting the column title - "Description" - in the cells. – Rob50 Jan 26 '15 at 17:27
  • @Rob50 Let's see the actual formula you are using. Copy/paste it; do NOT try to type it in. – Ron Rosenfeld Jan 26 '15 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.