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

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 SchuttSep 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-FoodSep 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.
–
ExcellllSep 7 '11 at 1:29

Hey Variant, I have been playing with this formula but so far it hasn't been working for me. When I enter it the way you have it, it tells me that there is a circular reference and returns "0". I think this is because the "E2" should be "F2", however, when I change to that, it comes back "#REF!" for the cell. What did I do wrong? I am entering the formula into cell E2, F2 is where the position code is. I also dropped the "1" off the very end because it said there are too many arguments for this function. My function looks like: =OFFSET(Master!A2, MATCH(Second!F2, Master!B1:B10,0), -1, 0, 1)
–
Andrew SchuttSep 6 '11 at 21:53

@Andrew, there is no comma before the -1. The ,1,1 at the end is suplerfluous anyway. What variant wrote is assuming you're using two worksheets named "Master" and "Second" in the same workbook. It's probably worth using the mouse to help create this formula. Where is says Master!$A$1, actually click on cell A1 in the master workbook and add the $ signs later. For Second!F2 click on the cell F2 in the second workbook. For Master!$B:$B, click on the B column heading in the master workbook. This will ensure the correct reference.
–
Hand-E-FoodSep 6 '11 at 23:08

-1 the OFFSET function is volitile, that is the cell will recalculate whnever the sheet calulates even when the cells it refers too have not changed value. this will make recalculating the sheet much slower that it needs to be. use INDEX instead
–
chris neilsenSep 7 '11 at 7:08

@chris - thanks for your input. I think it's a little poor to receive a downvote on an answer that's actually correct and reasonably well explained, but as you wish.
–
variantSep 7 '11 at 13:27