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 http://www.elementk.com by contacting survey or a quiz, depending on your Neil Davin, Technology Support Services Manager. neil_ interests. We will then go through the email@example.com. steps to post your syllabi.
Pages to are hidden for
"Microsoft Excel Using VLOOKUP to import data from"Please download to view full document