Description
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.
