Microsoft Excel Using VLOOKUP to import data from by mgr21059


									                                                                                                 June 2003

Microsoft Excel: Using VLOOKUP to                                       TEACHING AND LEARNING
import data from another list
                                                                   By Kimberly Hall
                                                                      Senior Instructional Designer
Microsoft Excelʼs VLOOKUP function allows you to import
data from a list or table into a spreadsheet by using a matching
look-up value such as ID number. This becomes very useful           WebCT Student Tips
when you are working with many columns and rows.
                                                                   There is a Student Tips feature in
The Syntax/Formula:                                                WebCT that communicates messages
                                                                   to students through a pop-up window.
      =VLOOKUP                                                     When students log into your course, a
(lookup_value,table_array,col_index_num,range_lookup)              small window appears with a message to
                                                                   your class. Messages can contain general
Lookup_value is the value found in the first column of             announcements or specific information
the array. Lookup_value can be a value, a reference or             on the course subject.
a text string. For example, a cell containing ID number.
                                                                   You can post a new tip anytime, and
Table_array is the table from which you want data                  the students can browse the list of tips
imported or looked up. You should give the table range             throughout the semester by clicking on
a name. The range must be placed in ascending order.               the tips link. To add Student Tips to your
                                                                   WebCT course, click Add Page or Tool
Col_index_num refers to the column number in the table             and then Student Tips.
array from which the value is imported. If you want data from
the third column of the table array, the col_index_num is 3.        WebCT Workshops
                                                                      June - July
Range_lookup specifies whether you want VLOOKUP
to find an exact match or an approximate match of the              WebCT is online course software with
lookup value. If Range_lookup is TRUE or left blank, an            many features intended to promote
approximate match is returned. An approximate match                learning and communication. Features
refers to the next largest value that is smaller than the          include discussion boards, links to library
lookup value, If Range lookup is FALSE, VLOOKUP will               e-reserves, a gradebook, file exchange
find an exact match of the lookup value. If one is not found,      and other communication tools. To
the error value #N/A is returned. If you are looking up ID         register for a workshop, please contact
numbers, you need to specify FALSE for the range_lookup.           Bret Kulakovich (ext. 8982) or Kimberly
                                                                   Hall (ext. 8961) at least one working day
VLOOKUP is best explained by the use of an example.                before the scheduled workshop.
Letʼs say you have a list containing students enrolled in
your department "AM" and you want to match it with the             Link to Library Articles & Book
Registrarʼs database to see who has withdrawn from the             Chapters Online; Providing Access to
College. The Department list contains the ID number, major,        E-Reserves
last and first names of students.                                  Individual sessions by appt. with Bob
                                           (continued next page)
                                                                   Felming, ext. 8696 (continued next page)
VLOOKUP (continued from front page)                                       WebCT (continued from front page)
Using CSVWriter you query the Registrar's database for an                This workshop introduces you to
updated list of all currently enrolled students who are AM majors.       WebCT, and Bob Fleming of the
                                       You use a FTP program to          Library will guide you to linking to
                                       download the completed file       selected journal and newspaper articles
                                       to your computer. Then you        in databases such as Academic Search
                                       insert this updated Registrar     Premiere, EBSCO and LexisNexis. We
                                       list as a second worksheet into   also help you post your syllabus.
                                       your workbook containing
figure 1: Insert Registrar's           the Department's list as          KEEP STUDENTS COMMUNI-
List as a second worksheet                          shown in figure 1.   CATING OUTSIDE CLASS TIME
1. On the Registrar Worksheet select                                       Thur June 12: 2:00 – 3:00
                                              figure 2: Type a name
the entire table range. Click in the          for the range in the         Thur June 19: 10:00 – 11:00
                                                           name box        Thur June 26: 2:00 – 3:00
Name box and type a name for this table,
such as ENROLLED_STUDENTS.                                                 Wed July 9:   2:00 – 3:00
(figure 2).                                                                Wed July 16: 10:00 – 11:00
2. In your Registrar's List, sort the                                      Wed July 23: 2:00 – 3:00
ID field in ascending order. (Data/Sort/                                 In this workshop we discuss the potential
Ascending).                                                              of WebCT’s communication and file
3. In the Department List, click on the first                            sharing features and offer assistance
cell where you want data to be placed.                                   with setting them up in your course.
4. Click on the Paste function button                                    Time is also allocated for posting
fx on your tool bar. Select "Lookup & Reference" from the                syllabi.
Function Category. From Function Name, scroll down and
select "VLOOKUP." Click OK. The VLOOKUP dialog box                       POST GRADES THROUGHOUT
appears. (figure 3).                                                     THE SEMESTER
                                figure 3    Cell containing value to
                                            look up (Sort Ascending)       Wed June 11: 10:00 – 11:00
                                                                           Tue June 17: 2:00 – 3:00
                                             Name of table containing
                                                       data to lookup      Tue July 8:  2:00 – 3:00
                                                                           Tue July 24: 10:00 – 11:00
                                                     Column number
                                                 containing data to be
                                                             imported    This workshop walks you through
                                              False = Absolute match     creating a gradebook that allows
                                                                         students to privately access their grades
5. The result is a                                                       online. We then go through the steps to
matching ID number                                                       upload your course syllabus.
for each student who is
still in the Registrarʼs                                                 ADMINISTER ONLINE SURVEYS
system, but a return of                                                  OR QUIZZES
the value "#N/A" for                                                       Tue June 10: 10:00 – 11:00
ID numbers not found                                                       Tue June 24: 2:00 – 3:00
in the Registrarʼs                                                         Thur July 10: 2:00 – 3:00
list.     (figure    4).                                                   Thur July 24: 10:00 – 11:00
                                                           figure 4      After reviewing the differences between
To learn more advanced concepts in Microsoft Excel or                    the WebCT survey and quiz, we will
other desktop programs, subscribe to the web-based, self                 write questions and create either a
paced courses at by contacting                   survey or a quiz, depending on your
Neil Davin, Technology Support Services Manager. neil_                   interests. We will then go through the                                                       steps to post your syllabi.

To top