up vote 2 down vote favorite
share [g+] share [fb]

I have 2 spreadsheets with department names and position codes. The master spreadsheet lists all the department names and position numbers, while the other only has position codes.

I need for the second spreadsheet to look at the position code and match it with the position code on the master spreadsheet, then copy the information from the cell to the left of the position code on the master spreadsheet, into the cell to the left of the position code in the other spreadsheet.

For Example:

Master Spreadsheet:

Column A = Department

Column B = Position code

Cell A2 = 70600: PACU

Call B2 = 2145

Second Spreadsheet:

Column E = Department

Column F = Position code

Cell E2 = (Blank) ← this is the information that needs to be filled from the master spreadsheet

Cell F2 = 2145

Column E of the second spreadsheet needs to be filled with the information from column A of the Master spreadsheet, based on the Position code listed in column F of the second spreadsheet matching the position code in column B of the master spreadsheet

link|improve this question
Are you trying to do this with VBA or just putting formulas into Excel manually? – Tom Zych Sep 6 '11 at 19:44
3  
Use the VLOOKUP function in Excel. contextures.com/xlfunctions02.html – Excellll Sep 6 '11 at 19:53
Hey Tom, I had thought that I could use a vlookup for this, but all I can figure out what to do with the vlookup is how to make a cell return true if it finds the position code on the master spreadsheet. I know I'm missing something, but I can't figure out what. – Andrew Schutt Sep 6 '11 at 20:18
@Excellll: VLOOKUP is restrictive. The list must be sorted and the values you return must be to the right of the value found. It's usually usseful, but won't work in this circumstance. (Unless it does more than I'm aware of...) – Hand-E-Food Sep 6 '11 at 22:59
@Hand-E-Food: You are correct. I quickly read through the (unedited) post and missed that the data to the left was needed. – Excellll Sep 7 '11 at 1:29
feedback

migrated from stackoverflow.com Sep 6 '11 at 20:11

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

1 Answer

up vote 2 down vote

Enter this formula in Cell E2 (second spreadsheet):

=INDEX(Sheet1!$A:$A,MATCH(F2,Sheet1!$B:$B,0))

Replace Sheet1 with the name of your master spreadsheet.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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