Microsoft® Excel Advanced Formulas: Logicals and Lookups

This more advanced Excel course will demonstrate to the user how to use formulas that extend across multiple worksheets, use conditional formulas namely in the Logical category, as well as exploring the deeper function category Lookup and Reference. Aggregate functions with testing conditions will be featured where mixed-cell referencing is involved along with formulas utilizing named ranges. In addition, this course will also delve into data mining and further will expose the user on how to create array formulas.

Objective: Upon completion of this course the Excel user will be able to work with logical and lookup functions while utilizing formulas with mixed-cell referencing, named ranges, and arrays. Featured functions include IF, OR, AND, formulas with Nested IF, SUMIF and SUMIFS, HLOOKUP and VLOOKUP, the LOOKUP function, as well as INDEX and MATCH.

Topics:

• Multi-sheet Formulas
  1. Grouping worksheets
• Logical Functions
  1. IF function
     1. AND & OR
     2. Nested IF statement

• Conditional Aggregate Functions
  1. SUMIF and SUMIFS
     1. Mixed-cell references

• Lookup Functions
  1. HLOOKUP
  2. VLOOKUP
     1. Using Named Ranges in formulas
  3. The LOOKUP Function
     1. Using Table Fields in formulas
  4. INDEX and MATCH
• Array Formulas
  1. The SUMPRODUCT Function
  2. Creating IF Array formulas