In this presentation author and Excel expert David H. Ringstrom, CPA, demystifies Excel’s family of lookup functions, guiding you from the legacy VLOOKUP formula to the more versatile XLOOKUP. David shows how to build lookup formulas from the ground up, using Excel’s built-in help tools to enter lookup values, select arrays, and troubleshoot common issues such as #REF! and #N/A errors. He contrasts approximate and exact match behavior, explains how to eliminate errors caused by extra spaces or AutoCorrect quirks, and demonstrates how to handle missing matches gracefully using IFNA. Along the way, you'll explore best practices such as future-proofing lookup formulas, using tables for structured referencing, and pairing INDEX with MATCH to enhance flexibility. Whether you’re stuck on VLOOKUP or ready to dive into XLOOKUP, this session will deepen your understanding and improve your lookup reliability.
David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.
Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.Who should attend:
Professionals seeking to use Microsoft Excel more effectively.
Topics typically covered:
- Finalizing and using the VLOOKUP function.
- Handing #N/A errors within XLOOKUP.
- Understanding the nuances of approximate matches with XLOOKUP, which can be greater than or less than the look-up value.
- Improving on older lookup methods by using the XLOOKUP function in Excel 2021 and later.
- Avoiding lookup errors by matching names exactly—punctuation and all.
- Future-proofing VLOOKUP by referencing entire columns when available.
- Replacing direct cell references with lookup functions such as XLOOKUP.
- Using SUMIF to total values for specific matching criteria.
- Avoiding lookup failures caused by sneaky spaces and subtle typos.
- Incorporating comparison operators within SUMIF to sum numbers based on range criteria.
- Stopping AutoCorrect from hijacking your text—one replacement at a time.
- Utilizing Excel’s IFNA function to display alternate values when VLOOKUP returns an error.
SKU *: crs-4840
Sell price $: 48.00
Course Preview: <iframe src="//player.vimeo.com/video/1156473507?autoplay=1" allowfullscreen="" frameborder="0" height="357" mozallowfullscreen="" webkitallowfullscreen="" width="635"></iframe>
Course Short Description:
In this presentation author and Excel expert David H. Ringstrom, CPA, demystifies Excel’s family of lookup functions, guiding you from the legacy VLOOKUP formula to the more versatile XLOOKUP.
Compliance Deep Tag: - None -
Credit Hours: 2.0
CPE Credits Awarded *: 2.0
Approval Status: - None -
Course is for: ** Main Course Library **
Course Duration: 2.0
Complexity of Content: Basic
Suppress in lists: No
Last Updated/Reviewed Date: January 27, 2026
Course Topic: Excel
Prerequisitses:
Prior experience with Microsoft Excel is recommended.
Learning Objective:
- Explore and state which XLOOKUP argument causes an alternate value to be displayed in lieu of #N/A.
- Discover and state the arguments for Excel's SUMIF function.
- Recognize and recall what to specify in the match_mode argument to cause XLOOKUP to return an approximate match based upon the next smaller item.
Course Type: CPE Course
crs_type:
CPE Course
Format: on_demand
format_crs:
on_demand
Audit & Accounting: No
Careers: No
Ethics (NASBA): No
Excel: Yes
Finance: No
Human Resources (HR): No
Internal Audit: No
Leadership & Management: No
Marketing: No
Operations: No
Productivity & Tech: No
Risk Management: No
Sales: No
Tax: No
Free Trail: No
Other: No
Field of Study: Computer Software & Applications
crs cat:
Excel
CPA: Yes
CPD-IES8: No
CMA: Yes
CFE: Yes
Yellow Book: No
CGFM: Yes
CIA: Yes
CRMA: Yes
CPP: No
FPC: No
CTP: No
FP&A: No
CISM: No
CRISC: No
CISA: No
CIST: No
CRFS: No
SHRM-CP: No
SHRM-SCP: No
PHR: No
SPHR: No
GPHR: No
aPHR: No
PHRca: No
PHRi: No
SPHRi: No
crma_irs: No
cert type:
CPA, CMA, CFE, CGFM, CIA, CRMA
Enable/Disable: Yes
CPE Credits CPA: 2.0
Field of Study CPA: Computer Software & Applications
CPE Delivery Method CPA: QAS Self-Study
Program Location CPA:
Online
*Program Level: Basic
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
Description:
Illumeo is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State Boards of Accountancy have the final authority on the acceptance of individual course for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
NASBA Sponsor Number: 109504
State of New York Sponsor Number: 002746
State of Texas Sponsor Number: 009890
In accordance with the standards of the National Registry of CPE Sponsors, CPE credits have been granted based on a 50-minute hour.
NASBA Sponsor Number: 109504
State of New York Sponsor Number: 002746
State of Texas Sponsor Number: 009890
Enable/Disable: No
Program Location CPD-IES8:
Online
Description:
Your specific provider of Continuing Professional Development accreditation has final authority on the acceptance of individual courses for CPD-IES8 credit.
Enable/Disable: Yes
CPE Credits CMA: 2.0
Field of Study CMA: Computer Software & Applications
CPE Delivery Method CMA: QAS Self-Study
Program Location CMA:
Online
*Program Level: Basic
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
Enable/Disable: Yes
CPE Credits CFE: 2.0
Field of Study CFE: Computer Software & Applications
CPE Delivery Method CFE: QAS Self-Study
Program Location CFE:
Online
*Program Level: Basic
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
Enable/Disable: Yes
CPE Credits CGFM: 2.0
Field of Study CGFM: Computer Science
CPE Delivery Method CGFM: QAS Self-Study
Program Location CGFM:
Online
*Program Level: Basic
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
Enable/Disable: No
Program Location YB:
Online
Description:
The GAO's Yellow Book, Government Auditing Standards, establishes CPE requirements. Auditors performing work under GAGAS, including planning, directing, performing field work, or reporting on an audit or attestation engagement under GAGAS, need to maintain their professional competence through continuing professional education (CPE). Therefore, each auditor performing work under GAGAS should complete, every two years, at least 80 hours of CPE that directly enhance the auditor's professional proficiency to perform audits and/or attestation engagements. At least 24 of the 80 hours of CPE should be in subjects directly related to government auditing, the government environment, or the specific or unique environment in which the audited entity operates. At least 20 hours of the 80 should be completed in any one year of the two-year period. For details on the Yellow Book CPE requirements go to http://www.gao.gov/ and https://www.gao.gov/assets/700/693136.pdf.
Enable/Disable: Yes
CPE Credits CIA: 2.0
Field of Study CIA: Computer science
CPE Delivery Method CIA: QAS Self-Study
Program Location CIA:
Online
*Program Level: Basic
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
Description:
Recognized CPE provider, authorized by the Institute of Internal Auditors for use in the Certified Internal Auditor (CIA) CPE program.
Recognized CPE provider, authorized by the Institute of Internal Auditors for use in the Certified Internal Auditor (CIA) CPE program.
Enable/Disable: Yes
CPE Credits CRMA: 2.0
Field of Study CRMA: Other activities contributing to professional competence
CPE Delivery Method CRMA: QAS Self-Study
Program Location CRMA:
Online
*Program Level: Basic
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
Description:
Recognized CPE provider, authorized by the Institute of Internal Auditors for use in the Certified Risk Management Assurance (CRMA) CPE program.
Recognized CPE provider, authorized by the Institute of Internal Auditors for use in the Certified Risk Management Assurance (CRMA) CPE program.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details CPP:
 | This activity, ID No. 19 ILL 000 has been approved for 1.0 Recertification Credit Hour(s) toward CPP recertification through the American Payroll Association (APA). Please make note of the Course Code on your CPP Recertification Log. For more information about certification or recertification, please visit the APA website at www.americanpayroll.org. |
Description:
This program has been approved for 12 Recertification Credit Hour(s) toward CPP recertification through the American Payroll Association (APA). For more information about certification or recertification, please visit the APA homepage at www.americanpayroll.org.
This program has been approved for 12 Recertification Credit Hour(s) toward CPP recertification through the American Payroll Association (APA). For more information about certification or recertification, please visit the APA homepage at www.americanpayroll.org.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details FPC:
| This activity, ID No. 19 ILL 000 has been approved for 1.0 Recertification Credit Hour(s) toward CPP recertification through the American Payroll Association (APA). Please make note of the Course Code on your CPP Recertification Log. For more information about certification or recertification, please visit the APA website at www.americanpayroll.org. |
Description:
This program has been approved for 12 Recertification Credit Hour(s) toward RCH recertification through the American Payroll Association (APA). For more information about certification or recertification, please visit the APA homepage at www.americanpayroll.org.
This program has been approved for 12 Recertification Credit Hour(s) toward RCH recertification through the American Payroll Association (APA). For more information about certification or recertification, please visit the APA homepage at www.americanpayroll.org.
Enable/Disable: No
Program Location CTP:
On Demand/Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
Description:
Approved for up to 2.4 CTP/CCM recertification credits by the Association for Financial Professionals
Approved for up to 2.4 CTP/CCM recertification credits by the Association for Financial Professionals
Enable/Disable: No
Program Location FP&A:
On Demand/Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
Description:
Approved for up to 2.4 FP&A recertification credits by the Association for Financial Professionals
Approved for up to 2.4 FP&A recertification credits by the Association for Financial Professionals
Enable/Disable: No
Program Location IRS-EA:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details IRS-EA:
 | This activity, ID No. XXXXXX has been approved for #### CPE credit hours toward IRS Enrolled Agent (EA) continuing education requirements. In accordance with the standards set forth in Circular 230 section 10.6, CE credits have been granted based on a 50-minute hour. |
Enable/Disable: No
Program Location CISM:
Online
Description:
Your specific provider of Continuing Professional Education accreditation has final authority on the acceptance of individual courses for CPE credit.
Enable/Disable: No
Program Location CRISC:
Online
Description:
Your specific provider of Continuing Professional Education accreditation has final authority on the acceptance of individual courses for CPE credit.
Enable/Disable: No
Program Location CISA:
Online
Description:
Recognized CPE provider, authorized by the Institute of Internal Auditors for use in the Certified Risk Management Assurance (CRMA) CPE program.
Recognized CPE provider, authorized by the Institute of Internal Auditors for use in the Certified Risk Management Assurance (CRMA) CPE program.
Enable/Disable: No
Program Location CIST:
Online
Description:
Your specific provider of Continuing Professional Development accreditation has final authority on the acceptance of individual courses for CPD credit.
Enable/Disable: No
Program Location CRFS:
Online
Description:
Your specific provider of Continuing Professional Development accreditation has final authority on the acceptance of individual courses for CPD credit.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details SHRM-CP:
 | This activity, ID No. XXXXXX has been approved for #### recertification credit hours toward SHRM-CP™ and SHRM-SCP™ recertification through the Society of Human Resource Management® (SHRM®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the Society for Human Resource Management website at www.shrm.org. |
Description:
Illumeo is recognized by SHRM to offer Professional Development Credits (PDCs) for the SHRM-CP℠. This program is valid for 2 PDCs for the SHRM-CP℠. For more information about certification or recertification, please visit www.shrmcertification.org.
Illumeo is recognized by SHRM to offer Professional Development Credits (PDCs) for the SHRM-CP℠. This program is valid for 2 PDCs for the SHRM-CP℠. For more information about certification or recertification, please visit www.shrmcertification.org.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details SHRM-SCP:
| This activity, ID No. XXXXXX has been approved for #### recertification credit hours toward SHRM-CP™ and SHRM-SCP™ recertification through the Society of Human Resource Management® (SHRM®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the Society for Human Resource Management website at www.shrm.org. |
Description:
Illumeo is recognized by SHRM to offer Professional Development Credits (PDCs) for the SHRM-SCP℠. This program is valid for 2 PDCs for the SHRM-SCP℠. For more information about certification or recertification, please visit www.shrmcertification.org.
Illumeo is recognized by SHRM to offer Professional Development Credits (PDCs) for the SHRM-SCP℠. This program is valid for 2 PDCs for the SHRM-SCP℠. For more information about certification or recertification, please visit www.shrmcertification.org.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details PHR:
 | This activity, ID No. 000000 has been approved for 1.0 HR (General) recertification credit hours toward aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification through HR Certification Institute® (HRCI®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the HR Certification Institute website at www.hrci.org. |
Description:
This program has been approved for 2 Business aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification credit hours through the HR Certification Institute. For more information about certification or recertification, please visit the HR Certification Institute homepage at www.hrci.org.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details SPHR:
| This activity, ID No. 000000 has been approved for 1.0 HR (General) recertification credit hours toward aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification through HR Certification Institute® (HRCI®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the HR Certification Institute website at www.hrci.org. |
Description:
This program has been approved for 2 Business aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification credit hours through the HR Certification Institute. For more information about certification or recertification, please visit the HR Certification Institute homepage at www.hrci.org.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details GPHR:
| This activity, ID No. 000000 has been approved for 1.0 HR (General) recertification credit hours toward aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification through HR Certification Institute® (HRCI®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the HR Certification Institute website at www.hrci.org. |
Description:
This program has been approved for 2 Business aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification credit hours through the HR Certification Institute. For more information about certification or recertification, please visit the HR Certification Institute homepage at www.hrci.org.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details aPHR:
| This activity, ID No. 000000 has been approved for 1.0 HR (General) recertification credit hours toward aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification through HR Certification Institute® (HRCI®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the HR Certification Institute website at www.hrci.org. |
Description:
This program has been approved for 2 Business aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification credit hours through the HR Certification Institute. For more information about certification or recertification, please visit the HR Certification Institute homepage at www.hrci.org.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details PHRca:
| This activity, ID No. 000000 has been approved for 1.0 HR (General) recertification credit hours toward aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification through HR Certification Institute® (HRCI®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the HR Certification Institute website at www.hrci.org. |
Description:
This program has been approved for 2 Business aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification credit hours through the HR Certification Institute. For more information about certification or recertification, please visit the HR Certification Institute homepage at www.hrci.org.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details PHRi:
| This activity, ID No. 000000 has been approved for 1.0 HR (General) recertification credit hours toward aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification through HR Certification Institute® (HRCI®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the HR Certification Institute website at www.hrci.org. |
Description:
This program has been approved for 2 Business aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification credit hours through the HR Certification Institute. For more information about certification or recertification, please visit the HR Certification Institute homepage at www.hrci.org.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
Enable/Disable: No
*Program Location:
Online
*Prerequisites:
No advanced preparation or prerequisites are required for this course.
~Credit Details SPHRi:
| This activity, ID No. 000000 has been approved for 1.0 HR (General) recertification credit hours toward aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification through HR Certification Institute® (HRCI®). Please make note of the activity ID number on your recertification application form. For more information about certification or recertification, please visit the HR Certification Institute website at www.hrci.org. |
Description:
This program has been approved for 2 Business aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification credit hours through the HR Certification Institute. For more information about certification or recertification, please visit the HR Certification Institute homepage at www.hrci.org.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
The use of this seal confirms that this activity has met HR Certification Institute’s® (HRCI®) criteria for recertification credit pre-approval.
Enable/Disable: Yes
Hours: 2.0
Free Marketing Course: No
Sponser: Illumeo
Participation: Solo
Instructor: Recorded
Presence: Remote