Excel tip- How do I add a column based on values in another column

I got this request today at work and thought I would share several fairly easy solutions.

The issue was that she had a spreadsheet that needed a new column based on data that was already in the spreadsheet.  In this example she had a column called region with a possible value of North, East, South or West.  She wanted to add in the manager responsible for these regions.  In this situation there are two managers and they split the regions, 2 each.

Solution 1:

• Copy the entire column, paste it into your new column and do a find and replace in the new column.  repeat this for all 4 regions.  Make sure you only have the one column selected so you don't replace the values from the original column which should still hold the region info.

Solution 2:

• Sort the region column and then in the first cell in your new column type the managers name.  Drag the value (using the heavy crosshair cursor you get when you hover over the bottom right corner of the cell) until you have filled all the values for that region and then repeat for the other 3 regions.

and Solution 4:  

• Use a formula! 

• In this example we will actually need two formulas (OR and IF).  Lets break them down one by one.

• Lets start with IF which will take three values one of which will be our OR function, more on that later.  First it will take some condition that it will test and see if it is true or false.  If it is true it takes the second value and if it is false it will take the third value.  In sentence format it would look like this:
  
  • If "this is true", "then do this", "otherwise do this".   
  • In our example it is going to be "If the region is 'South' OR the region is 'North' then use the name 'Smith', other wise use 'Jones'"
•  In Excel's syntax it will look more like this  =IF(logical test,"Smith","Jones").  
• Now lets look at the OR function which is needed since we have two conditions that might require putting in the same value.
• Or takes as many logical conditions as you want to test (not sure if there is a limit) and returns TRUE if there is one that is true and FALSE if they are all false.
• For our example we would do something like this =or(A1 = "South", A1 = "North").  This will simply return "True" if the value in A1 is either South or North which is perfect for our IF statement because as you remember, the first thing that it is looking for is a condition to test to see if it is true or false. 

Now lets put it all together:
=IF(OR(A1 = "South", A1 = "North"),"Smith","Jones")

Drag the formula to all the cells in the report (or double click the dark crosshair) and you are good to go!