Pragmatic Excel - Introduction to Lookup Functions

Who Would Be Interested in This Course:

Practitioners who wish to learn how to implement a variety of lookup functions, which will allow them to avoid manually linking to specific cells and work more efficiently in Excel.

Topics Covered:

•        Comparing the MIN, SMALL, MAX, and LARGE functions. 

•        Employing the SUMIF function to sum values related to multiple instances of criteria you specify. 

•        Exploring why VLOOKUP sometimes returns #N/A instead of a desired result. 

•        Future-proofing VLOOKUP by referencing entire columns when available. 

•        Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges. 

•        Implementing Data Validation to ensure users make choices that VLOOKUP will recognize as valid. 

•        Improving the integrity of spreadsheets with Excel’s VLOOKUP function. 

•        Learning what types of user actions can trigger #REF! errors. 

•        Reconstructing spreadsheet data to use VLOOKUP as a better alternative to nesting IF functions. 

•        Simplifying multiple-field lookups with concatenation (combining fields into a single cell). 

•        Troubleshooting other errors VLOOKUP can present, such as #REF!, #NAME!, and #VALUE!. 

•        Understanding situations that cause Excel’s VLOOKUP function to return #REF! instead of a value.

Learning Objectives:

•        Define the purpose of Excel’s IFERROR worksheet function.

•        Identify what you can use in place of the word “TRUE” in VLOOKUP to return an approximate match.

•        Recall the keyboard shortcut that toggles absolute references within a formula.

Level:

Basic