Lookup Functions Part 2

Astute Excel: Lookup Functions Part 2 Presented by David H. Ringstrom, CPA Excel expert David Ringstrom, CPA, explains helpful ways...

0
(0 rating)
10 students
Last updated February 5, 2024
Current Status
Not Enrolled
Price
9.95
Get Started

Astute Excel: Lookup Functions Part 2

Presented by David H. Ringstrom, CPA

Excel expert David Ringstrom, CPA, explains helpful ways you can improve the integrity of your spreadsheets using Excel’s lookup functions. The presentation leads with a comparison of exact and approximate matches with VLOOKUP, and then comparisons to the HLOOKUP and LOOKUP functions. The presentation then covers a variety of troubleshooting techniques for VLOOKUP, and wraps up with a discussion of other alternatives such as MATCH/INDEX, SUMIF, and SUMIFS.

Topics Covered:

  • Using the MATCH function to find the position of an item in a list.
  • Utilizing Excel’s IFERROR function to display alternate values when VLOOKUP returns an error.
  • Removing the Table feature from a worksheet if it’s no longer needed.
  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP
  • Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
  • Avoiding the complexity of nested IF statements with Excel’s CHOOSE function.
  • Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
  • Enabling VLOOKUP to look up data from the left (instead of only from the right) by using the CHOOSE function.
  • Transforming numbers stored as text into values by way of the Text to Columns wizard.
  • Learning about the IFNA function available in Excel 2013 and later.
  • Identifying situations where VLOOKUP may return #N/A instead of a value.

Learning Objectives:

  • Identify what you can use in place of the word TRUE in VLOOKUP to return an approximate match.
  • Recall the maximum number of criteria pairs that the SUMIFS function permits.
  • State the purpose of the IFERROR function.