Look Who it Was Random video

Excel - Troubleshooting #N/A Errors in VLOOKUP: Common Causes and Solutions - Episode 1122

816 views · Published 14 October 2009 · 3:08 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Troubleshooting #N/A Errors in VLOOKUP: Common Causes and Solutions.

Welcome back to the MrExcel netcast, where we tackle all things Excel. In this episode, we're diving into the frustrating issue of #N/A errors in VLOOKUP. We've all been there - you have a perfectly functioning VLOOKUP formula, but suddenly, all of your results are showing #N/A. What gives?

Well, in this video, we'll explore the common causes of this problem and how to fix it. First, we'll take a look at trailing spaces in our data. These spaces are often added in COBOL data sets and can throw off our VLOOKUP formulas. But don't worry, there's a simple solution - the TRIM function. By using TRIM, we can remove any leading or trailing spaces from our data and get our VLOOKUPs back on track.

But what if the problem is the opposite? What if our lookup table has the spaces, but our data does not? Don't worry, we have a solution for that too. By using the TRIM function and copying the values over, we can ensure that our VLOOKUPs will work correctly. We'll also touch on the issue of numbers stored as text and how to fix that to avoid #N/A errors.

Another common cause of #N/A errors is mismatched upper and lower case letters. While Excel has no problem with this, it's the invisible spaces that can cause issues. So, it's always a good idea to check for any hidden spaces in your data before troubleshooting further.

Thanks for tuning in to this episode of the MrExcel netcast. Don't forget to hit that like button and subscribe for more Excel tips and tricks. And as always, if you have any questions or suggestions for future videos, leave them in the comments below. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction to VLOOKUP and Pivot tables
(00:14) Troubleshooting #N/A errors in VLOOKUP
(00:31) Identifying and removing trailing spaces in data
(01:02) Using the TRIM function to remove spaces in data
(01:28) Dealing with spaces in lookup tables
(02:08) Other common causes of #N/A errors in VLOOKUP
(02:44) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Checking for leading or trailing spaces in data
Converting numbers stored as text for VLOOKUP
Dealing with leading spaces in lookup tables
Fixing #N/A errors in VLOOKUP
Handling upper and lower case differences in VLOOKUP
Invisible spaces causing VLOOKUP errors
MrExcel netcast on data analysis
Removing trailing spaces in COBOL data sets
Tips for resolving VLOOKUP issues
Troubleshooting VLOOKUP errors
Using TRIM function to remove leading and trailing spaces
YouTube video on analyzing data with Pivot tables


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152688/

What happens when you enter the perfect VLOOKUP formula and everything returns #N/A? Episode 1122 shows you some of the sneaky reasons why VLOOKUPs fail and what to do about it.

This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!

More from this channel