MS_EXCEL-VLOOKUP. power point presentation

VLOOKUP PPT

1. EMPOWERMENT TECHNOLOGIES Let’s Recall

2. EMPOWERMENT TECHNOLOGIES MICROSOFT EXCEL

3. EMPOWERMENT TECHNOLOGIES =SUM =AVERAGE =COUNT COMMON FUNCTION

4. EMPOWERMENT TECHNOLOGIES =SUM =SUM(RANGE) COMMON FUNCTION

5. EMPOWERMENT TECHNOLOGIES =AVERAGE =AVERAGE(RANGE) COMMON FUNCTION

6. EMPOWERMENT TECHNOLOGIES =COUNT =COUNT(RANGE) COMMON FUNCTION

7. EMPOWERMENT TECHNOLOGIES CONDITIONAL FUNCTION =SUMIF =AVERAGEIF =COUNTIF

8. EMPOWERMENT TECHNOLOGIES CONDITIONAL FUNCTION =SUMIF(RANGE,CRITERIA,SUM_RANGE) =SUMIF

9. EMPOWERMENT TECHNOLOGIES CONDITIONAL FUNCTION =AVERAGEIF(RANGE,CRITERIA,AVE_RANGE) =AVERAGEIF

10. EMPOWERMENT TECHNOLOGIES CONDITIONAL FUNCTION =COUNTIF(RANGE,CRITERIA) =COUNTIF

11. EMPOWERMENT TECHNOLOGIES Microsoft Excel Function VLOOKUP

12. Advance and Complex Calculations in Excel Complex formula is the combination of more than two simple formulas. One of the key features of excel is the ability to calculate complex formulas.

13. Advance and Complex Calculations in Excel There are four basic computation uses in excel. Namely addition, subtraction, multiplication, and division.

14. Advance and Complex Calculations in Excel When solving complex problems, Excel follows the PEMDAS.

15. Advance and Complex Calculations in Excel PEMDAS is an acronym for the words: P arenthesis, E xponents, M ultiplication, D ivision, A ddition, and S ubtraction.

16. Cell Reference A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate.

17. Relative Reference All cell references are called relative references. When copied across multiple cells, they change based on the relative position of rows and columns.

18. Absolute Reference These are cell references that do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant .

19. Absolute Reference These reference cells can be made constant by inserting $ sign in between or before the row and column.

20. Absolute Reference $A$2 The column and the row do not change when copied. A$2 The row does not change when copied. $A2 The column does not change when copied.

21. EMPOWERMENT TECHNOLOGIES Microsoft Excel Function VLOOKUP

22. Data Validation Excel data validation is a feature that allows you to control the type of data entered into your worksheet. 

23. Data Validation For example, Excel data validation allows you to limit data entries to a selection from a dropdown list and to restrict certain data entries, such as dates or numbers outside of a predetermined range.

24. VLOOK_UP The VLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

25. VLOOK_UP Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.

26. Syntax =VLOOKUP( lookup_value , table_array,col_index_num , [ range_lookup ])

27. Syntax There are four pieces of information that you will need in order to build the VLOOKUP syntax: The value you want to look up, also called the lookup value. The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.

28. Syntax The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on. Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don't specify anything, the default value will always be TRUE or approximate match.

29. VLOOK_UP =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

30. Syntax