Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

There are two excel sheets I'm working on. The first is made up of 300 names of programs, one each row, and with 2 additional columns with version numbers and extra info. The second is a lot alike, but has about 100 rows and has 2 columns with different information from the first.

example:

Adobe premiere , 6.0.2, video-editing software

Around 50 of these programs match by name (they are both in sheet 1 and 2), and i would like to import extra information about all matching programs from sheet 2 into sheet one, next to the existing information. That would mean that 50 or so rows have 2 additional columns and that about half of sheet 2 is left unmatched.

By hand, this means copying the names from sheet 2, finding a match in sheet one, then copying the 2 columns from sheet 2 and pasting them in the corresponding location in sheet 1. I hope there might be a better way to do this. Does anyone know a way?

Any help is appreciated.

share|improve this question

migrated from stackoverflow.com Jan 31 '13 at 2:25

This question came from our site for professional and enthusiast programmers.

2 Answers 2

You can achieve what you require by using a combination of VLOOKUP and IFERROR: Enter the following formula in your first worksheet and replace the name of the second workbook and worksheet:

=IFERROR(VLOOKUP(A2,'[Workbook2Name.xlsx]Sheetname'!$A:$C,2,0),"")

This should return you the second column of the matching entry (assuming that the data is stored in columns A-C).

share|improve this answer

Though I may be misinterpreting your question, I read it to mean that you want to return data from two columns in Sheet2. If that's the case, @Peter's formula won't quite work. That is because VLOOKUP will return data from one column only.

The following formula would do the job. For illustrative purposes, I assume the program names in Sheet1 begin in cell A2 and your data in Sheet2 are in cells G2:I11.

  =IFERROR(OFFSET(Sheet2!$G$2,MATCH(A2,Sheet2!$G$2:$G$11,0)-1,1,1,2),"-")

This is an array formula. To enter it, you would highlight cells B2:B3 in Sheet1, input the formula, making sure to complete the entry with the CONTROL-SHIFT-ENTER key combination. Then you would copy it down to the end of the data in Sheet1.

It works by using MATCH to find the row in which the program name in Sheet1 matches the program in Sheet2 (relative to the first data cell in Sheet2). It then uses OFFSET to return the corresponding two cells of data from Sheet2.

As @Peter notes, if second sheet is in another workbook, you would need to preface the references to that sheet with the workbook name, and if the sheet has a name other than the default, you would need to use the sheet name in the formula as well, resulting in:

  =IFERROR(OFFSET('[Workbook2Name.xlsx]Sheet2name'!$G$2,MATCH(A2,'[Workbook2Name.xlsx]Sheet2name'!$G$2:$G$11,0)-1,1,1,2),"-").
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.