Mastering VLOOKUP in Excel to Calculate Days Enrolled

1. Using “vlookup” function in Excel to complete “Days Enrolled” Howard De Leeuw, OSPI howard.deleeuw@k12.wa.us 360-725-6147

2. Open both your downloaded file and your newly-created “Days Enrolled” file in Excel

3. In your downloaded file, click in the first cell under the “DAYS_ENR” column

4. Go to “Insert Function” search for “vlookup”, highlight it and click “OK”

5. A “function arguments” window will open – click on the icon next to the “Lookup_value” field

6. Select the entire “EDATE” column, then click on the icon at the far right of the “Function Arguments” window

7. Now back at the main window, click on the icon at the end of the “Table_array” field.

8. Now go to your “Days Enrolled” file that you created and select both the “Date” and “Day” columns, then click the icon on the far right.

9. You are now back to your main file and main “Function Arguments” window. Enter “2” in the Col_index_num” field

10. In the “Range_lookup” field enter “false” and then click “OK” at the bottom of the window.

11. If everything went as planned, you should see a number of days in the cell.

12. Now you need to drag the formula from the first cell to the bottom of your spreadsheet – this will then apply the calculation to every cell.

13. Once all the cells of days, select the entire column.

14. Right click on the selected column and select “Copy”

15. Right click on the column again and select “Paste Special…”

16. When the “Paste Special” window opens, select “Values” and click OK – this will preserve your data and remove the formula.

17. Back to main powerpoint