Microsoft® Excel Advanced Formulas: Logicals and Lookups

Click Here For More Information

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.


  • 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
Course Information
  • Course Level:Intermediate
  • Prerequisite:Experience with Excel and basic formulas.
  • Credit:4 CPE hours
  • Fee:$199
  • Length:half day
  • Delivery:Instructor Led/Virtual Live
  • Recommend Field of StudyComputer Applications
  • Program Knowledge LevelIntermediate
  • Advanced PreparationDownload and install class files a minimum of 3 working days prior to class.
  • Refund PolicyRequests for cancellations must be submitted in writing and received by Knowledge Source Inc. five (5) days prior to the date of the event to receive a full refund (less a 10% processing fee). Refunds are not given for no-shows. Special circumstances and cancellation requests may be emailed to or by calling 919-878-7100. Refunds will be issued within 30 days after the course.

Related Courses