Download VLOOKUP Function: Combining Data from Different Excel Documents and more Summaries MS Microsoft Excel skills in PDF only on Docsity!
VLOOKUP Function Purpose
(Mac Guide)
Let’s say that you have two different Excel documents. Each document has different student achievement or outcomes data. In this example, the first document has DIBELS data. The second document has FAST CBM-R data. You want to combine these two documents but definitely do not want to retype any information because of the time it will take and possible errors that may arise. VLOOKUP is your answer if both documents have a reliable common data element, such as students’ state identification numbers, across both Excel documents.
In the example below, Mrs. Disney has this exact situation and she is going to solve this issue by transferring the FAST CBM-R data into the primary Excel document, where the DIBELS is already located. VLOOKUP can be used with any type of data that you want included in the primary Excel document as long as there is a reliable common data element. The issue is getting multiple sources of student data onto one Excel document.
Mrs. Disney can continually update her primary Excel convergent data document as additional data elements emerge across the school year. This gives her an updated picture of students’ growth/achievement/primary as new data sources are dropped into the primary Excel document. This information also provides teachers a picture of how their students are progressing and helps them evaluate their effectiveness. This Excel document can be used in PLCs, Data Teams, BASE teams and the like, to help focus the conversation around student learning and teacher effectiveness. Follow the steps below.
VLOOKUP Function in Excel:
- Create or open the Excel workbooks that contain the different information you want. We will call the Excel workbook where you want to put the data (from secondary Excel workbooks) the Primary file.
Secondary Excel workbooks---limit your information to state student ID numbers and student scores only
- Within your Primary file, click on one cell where you want to put the data from the secondary Excel workbook.
- Click the “fx” button.
This will open a “Formula Builder” box.
- Select the cell (in the same row as your Primary file cell) that contains your identifier. The identifier is the phrase or number that is unique and is located in both the Primary file and the secondary Excel workbook. Example identifier: State student ID number.
Once you select the cell, it will appear in the “lookup_value” text box.
- Click on the text box labeled “table_array.” Now pull up the secondary Excel workbook.
- Click on the text box labeled “col_index_num.”
- Go to the secondary Excel workbook. Begin counting the column with your identifier and continue counting until you come to the column containing the data you want to put into the primary file.
Type in the number of columns you counted into the text box.
- Hit “Enter” on your keyboard and the data you wanted from the secondary Excel workbook should appear in the Primary file cell you originally selected.
- If you want to use the VLOOKUP function for the whole column, you can click on the first cell with the function and hold your mouse over the bottom right corner. When the mouse turns into a small black plus sign, click down and drag all the way to the bottom of the column.
The function’s formula should be copied to each cell in the column and the correct data should appear if it was located in the same secondary Excel workbook as the beginning cell.
We recommend that you save the information you pulled with VLOOKUP as value data, so that it is no longer attached to the VLOOKUP formula.
- Highlight the cells that contain your VLOOKUP information by clicking on the first cell (FAST CBM-R heading) and dragging down; right click and then select “Copy.”
Common Issues with VLOOKUP
Number stored as text (#N/A error)
When you pull a report from EdInsight, check the “State Student ID” cells for small green triangles in the upper left corners and corresponding exclamation marks/error messages:
The “State Student ID” information tends to pull as text instead of number data, which causes an error with the VLOOKUP formula. To fix this issue, highlight all of the cells containing “State Student ID” numbers. Hover your mouse over the exclamation mark/error message and click on the “Convert to Number” option:
No information available (#N/A error)
If you have a student in your Primary Excel file who is not included within your secondary workbook (Ex: student recently moved to the area – no scores available), VLOOKUP will return a #N/A result:
NOTE: To obtain details about an error – hover your mouse over the exclamation mark/error icon to obtain the pop up window explanation.
Receiving same result all the way down the column
- Open your Excel document
- File
- Options
- Formulas
- Calculation options
- Select “Automatic”
- Ok
- Save document
If “Manual” is selected on an Excel document, VLOOKUP will not function properly within the spreadsheet
