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.

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

share|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
add comment

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

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

2 Answers

I think your best option is to use OFFSET and MATCH together.

A basic OFFSET looks like this:

 =OFFSET(A1, [numrows], [numcols], [height], [width])

where:

  • A1 is the cell from which you are offsetting
  • [numrows] is the number of rows you are offsetting by (positive [down] or negative [up])
  • [numcols] is the number of columns to offset by (positive [right] or negative [left])
  • [height] is the number of rows you want to return - use 1 for a single cell
  • [width] is the number of columns you want to return - use 1 for a single cell

A basic MATCH, which returns the number of cells over or down in a lookup array a given lookup value is located, looks like this:

 =MATCH([lookup],[region],[matchtype])

where:

  • [lookup] is the value you want to look up (in your case, the position code)
  • [region] is the area where you need to search
  • [matchtype] should be 0 for an exact match

In your case, (assuming your sheets are named "Master" and "Second", respectively, your formula would look something like:

 =OFFSET(Master!A1, MATCH(Second!E2,Master!$B$1:$B$100,0)-1, 0, 1, 1)

You might have to tweak the MATCH by subtracting 1 (as I did above) depending on which cell you offset from vs where you start your MATCH array.

share|improve this answer
    
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 Schutt Sep 6 '11 at 21:53
    
@variant: Nice! You taught me something there. A slight change: =OFFSET(Master!$A$1, MATCH(Second!F2,Master!$B:$B,0)-1, 0) –  Hand-E-Food Sep 6 '11 at 23:04
1  
@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-Food Sep 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 neilsen Sep 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. –  variant Sep 7 '11 at 13:27
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

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

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