Microsoft Office Grade 10 A / B

1. Microsoft Office Grade 10 A / B 16 Microsoft Excel – 07 Cahaya Bangsa Classical School (C) 2010 Digital Media Production Facility

2. 16 – Microsoft Excel - 07 OUTLINE | 2 OUTLINE • Objective • Introduction • “VLOOKUP” Worksheet Function • “HLOOKUP” Worksheet Function • “VLOOKUP” example Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

3. 16 – Microsoft Excel - 07 Objective | 3 Understand Microsoft Excel basic reference worksheet functions “vlookup” and “hlookup” Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

4. 16 – Microsoft Excel - 07 Introduction | 4 • Introduction • Categorized as “Reference” worksheet function • Mostly contain nested function (function within function) • Reference table MUST be sorted in ascending Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

5. 16 – Microsoft Excel - 07 “IF” Worksheet Function | 5 • “VLOOKUP” Worksheet Function • Purpose: to search and return value of any given reference table, arranged in vertical • Usages: as any other formula, always begin with “=“ • =VLOOKUP(lookup_value,table_array,column_index_number,[false]) • lookup_value is the value that match the criteria of the most left value in the reference table • table_array refers to the complete reference table • column_index_number must return the value as number (1,2,3,…) refers to the column # • [false] will be used to match for the second best match • cell locking may applies Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

6. 16 – Microsoft Excel - 07 “IF” Worksheet Function | 6 • “HLOOKUP” Worksheet Function • Purpose: to search and return value of any given reference table, arranged in horizontal • Usages: as any other formula, always begin with “=“ • =HLOOKUP(lookup_value,table_array,column_index_number,[false]) • lookup_value is the value that match the criteria of the most left value in the reference table • table_array refers to the complete reference table • column_index_number must return the value as number (1,2,3,…) refers to the column # • [false] will be used to match for the second best match • cell locking may applies Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

7. 16 – Microsoft Excel - 07 “IF” Worksheet Function | 7 Reference Table Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

8. 16 – Microsoft Excel - 07 “IF” Worksheet Function | 8 Application MUST ASCENDING REFERENCE TABLE MUST BE FILLED Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

9. 16 – Microsoft Excel - 07 “IF” Worksheet Function | 9 Application Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

10. 16 – Microsoft Excel - 07 “IF” Worksheet Function | 10 Application Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

11. 16 – Microsoft Excel - 07 “IF” Worksheet Function | 11 Application USD in column 2, SGD in 3, and IDR in 4 Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility

12. 16 – Microsoft Excel - 07 THANK YOU | 12 THANK YOU Cahaya Bangsa Classical School Microsoft Office(C) 2010 Digital Media Production Facility