excel ebook

TEACHERS WORKBOOK ON EXCEL: VLOOKUP who?how what?why? when? where?

COMPUTER APPLICATIONS TECHNOLOGY FOREWORD by the MINISTER Our national curriculum is the culmination of our efforts over a period of seventeen Years to transform the curriculum bequeathed to us by apartheid. From the start of Democracy we have built our curriculum on the values that inspired our Constitution (Act 108 of 1996). The Preamble to the Constitution states that the aims of the1 MRS ANGIE MOTSHEKGA, MP Constitutions are to:MINISTER OF BASIC EDUCATION • heal the divisions of the past and establish a society basedon democratic values, social justice and fundamental human rights;• improve the quality of life of all citizens and free the potential of each person;• lay the foundations for a democratic and open society in which government is based on the willof the people and every citizen is equally protected by law; and• build a united and democratic South Africa able to take its rightful place as a sovereign state inthe family of Nations.Education and the curriculum have an important role to play in realizing these aims. In 1997 weintroduced outcomes-based education to overcome the curricular divisions of the past, but theexperience of implementation prompted a review in 2000. This led to the first curriculumrevision: the Revised National Curriculum Statement Grades R-9 and the National CurriculumStatement t Grades 10-12 (2002).Ongoing implementation challenges resulted in another review in 2009 and we revised theRevised National Curriculum Statement (2002) to produce this document.From 2012 the two 2002 curricula, for Grades R-9 and Grades 10-12 respectively, arecombined in a single document and will simply be known as the National Curriculum StatementGrades R-12. The National Curriculum Statement for Grades R-12 builds on the previouscurriculum but also updates it and aims to provide clearer specification of what to be taught andlearnt on a term-by-term basis. 2

AIMS OF THIS DOCUMENTAims of the South African Curriculum:• understand the concepts of ICTs with regard to the technologies that make up acomputing system;• understand the various technologies, standards and protocols involved in theelectronic transmission of data via a computer-based network;• use the Internet and the WWW and understand the role that the Internet plays as partof the global information superhighway;• find authentic and relevant information, process the information to draw conclusions,make decisions and communicate the findings in appropriate presentation media; and• recognize the legal, ethical, environmental, social, security and health issues related tothe use of ICTs and learn how to use ICTs responsibly.SPECIFIC AIMS OF THE LESSONLearners should be able to know: ∑ What is the purpose of vlookup? ∑ What function is used to achieve vlookup? ∑ What is a look up value? ∑ What is the table array? ∑ What is a column index? ∑ How to lock cells?RESOURCES REQUIRED ON THIS LESSON ∑ Computers ∑ Overhead projector ∑ Internet connectionTEACHING PLAN ∑ Lessons on VLOOKUP formula ∑ Vlookup examples ∑ Vlookup Errors & Fixing themASSESSMENT 3

Table of ContentsBackground page .......................................................................................................................................... 1 Foreword .................................................................................................................................................. 2 Aims of this document ......................................................................................................................... 3Vlookup ........................................................................................................................................................ 4 How to achieve the vlookup ..................................................................................................................... 5 The range .............................................................................................................................................. 6 4

VLOOKUPThe purpose of the vlookup function is to look for a value in a list and returns the resultsfound within the same row of the value (in the 2nd Column, 3rd column , etc) of the list.Vlookup worksheet function will deliver different results, depending on what has beenprovided in the formula. Therefore it is important that we understand how the vlookupworks.First, let’s start with a simple example, assuming you have a list given below: ∑ In cell A10, enter the vlookup function given here (without inverted commas): \"=VLOOKUP(70901005,$A$1:$B$7,2)\" ∑ You will notice that the cell will return the result \"25 days\". It is the value given in Cell B5. Let's find out what was the instruction given in the vlookup formula that leads to this result. ∑ In the formula, we have indicated in the vlookup formula that we are looking for the value \"70901005\" vertically (from range A1 to A7). When the value is found, it was instructed to return the value in the 2nd column denoted by the number 2 in the formula \"=VLOOKUP(70901005,$A$1:$B$7,2)\". ∑ Note that we have entered the range $A$1:$B$7 into the formula. The range B1 to B7 is included because we need to tell Excel to look for the results within a specific range. If we are to include a larger range into the formula, it will take up more memory as Excel would have to look through more rows for the values. ∑ Now let's replace the value \"70901005\" with \"70901002\" which is not found in the list. In this case, Excel will return the results associated with a value in Column A that is smaller than the lookup value (\"70901002\"). Since \"70901001\" is smaller than \"70901002\", Excel will accept this value and gives you the results \"COD\". Do note that the Invoice No must be arranged in ascending orders (smallest to the largest number) for the vlookup function to work. 5

∑ If we are to enter the word \"false\" at the end of the formula like this =VLOOKUP(70901005,$A$1:$B$7,2,FALSE), you will notice that the results turns into \"#N/A\". The word \"false\" has triggered another instruction to Excel. We have indicated that we do not want the vlookup function to return any result if the value (\"70901002\") cannot be found. When we omitted this part of the formula, Excel by default assumes that we want to find the closest match to the value. Hence, \"COD\" was returned when we did not enter the word \"false\" into the formula.For more information: ∑ https://padlet.com/nonhlanhleemaseko/imcihdd1hl7x ∑ https://b.socrative.com/teacher/#lauch 6

7