Looking for a reliable way to combine data from two different sources?                                        Main file:
     Grab your favorite Excel buddy and try this out.                                                                                                        Behavior  Courses 
                                                                                                                                                    Total       (#     Failed (out 
     1.     Start with two data files open in Excel: a main file, and a file with new data.                       Student ID First Name Last Name Absences suspensions) of all courses)
     2.     Each file can have student data in rows (one row = one student), with other data                      100000001 Student 1   Student 1     9                    2
            fields in columns. Make sure there’s a student identifier – such as SASID or LASID                    100000002 Student 2   Student 2     7
            – in both files. This is what you’ll use to match the data from the two files                         100000003 Student 3   Student 3     5         1
            (sometimes called the link or the key).  In the file with new data, put the key in                    100000004 Student 4   Student 4     3                    1
            the far left column.                                                                                  100000005 Student 5   Student 5     1
     3.     In the main file, insert a new sheet. Copy the new data and paste it onto the new                     100000006 Student 6   Student 6     0
            sheet. Now you can work from the main file. 
     4.     Make a new column where you’d like the new data to go, and name the column. 
            Copy this function into your new column, right under the column name:                                    Sheet with new data:
              =INDEX('EWIS risk levels (new data)'!$A$2:$D$7,MATCH(A2,'EWIS risk levels (new                                                              EWIS Risk 
                    data)'!$A$2:$A$7,0),4)                                                                            Student ID  First Name  Last Name     Level
     5.     Index & Match are Excel functions.  Google ‘index match’ to learn how it works;                           100000001   Student 1    Student 1     High
            here are main pieces:                                                                                     100000002   Student 2    Student 2   Moderate
              •     Blue=the name of the sheet that has your new data, in quotes, plus an !                           100000003   Student 3    Student 3     Low
              •     Purple=the range where your ‘key’ is, in the new data sheet (and $ keeps the                      100000004   Student 4    Student 4   Moderate
                    exact range when you copy & paste this into other rows or columns)                                100000005   Student 5    Student 5     Low
              •     Orange=the range of your data, from the key to the new data field                                 100000006   Student 6    Student 6     Low
              •     Red=make this 0, as you want the key fields to match exactly
              •     Green=starting with key as #1, count the columns going to the                Main file with new column (far right):
                    right, until you get to your NEW field; that # goes here                                                                                Courses    EWIS Risk 
              •     Brown=a key for one student                                                                                       Total    Behavior (#  Failed (out of  Level (from 
                                                                                                Student ID  First Name  Last Name   Absences suspensions) all courses)  new data)
     6.     Edit the blue, purple, orange, red, green and brown to                              100000001    Student 1   Student 1      9                      2      High
            match your data file. Then copy this, and paste it                                  100000002    Student 2   Student 2      7                              
            going down the column. You should see that data                                     100000003    Student 3   Student 3      5          1                   
            from your other sheet fill in.                                                      100000004    Student 4   Student 4      3                      1       
     There are more EWIS Excel tips at ESE’s EWIS  website!                                     100000005    Student 5   Student 5      1                              
                                  http://www.doe.mass.edu/ccr/ewi/                              100000006    Student 6   Student 6      0                              
                                                                                                                                                                         EWIS