If that’s the case, you’ll make sure you get rid of that too or the error won’t leave you alone. However, the extra spaces could be in your lookup table as well.
With this trick, even if there happen to be leading and trailing spaces in your lookup value, the trim function will always handle that for you. Thus, if the extra spaces are in the lookup value (cell B6), instead of looking up B6, look up TRIM(B6). The trim function removes extra spaces from your cells. Extra spaces in the main table (lookup column) can cause the error just like extra spaces in the lookup value.Īnother way to solve this problem is to use the TRIM function within the VLOOKUP formula. The solution to this kind of Excel VLOOKUP not working problem is to check and see where there are extra spaces and get rid of them. This is caused by extra spaces which your eyes can hardly see, especially when dealing with a large volume of data.
Well, this kind of error takes more than just the eyes to see. But why do you have this error message? Why can you see this but Excel cannot? This time around the name is spelled correctly. The data, as well as the VLOOKUP formula, is the same as the previous example. Like in the above case, the problem disappeared as soon as the user fixe the problem by typing the employee name correctly. The solution to this kind of Excel VLOOKUP not working problem is simply to retype the mistyped value correctly. But when the name was typed correctly, the formula was now able to find the specified employee’s salary. For you, these two names may sound the same but Excel sees them as different names.Īs seen in the above screenshot, the VLOOKUP function displayed the #N/A error message when the employee name was mistyped. If for any reason you mistyped the lookup value, the function will not find what you typed and will definitely give you an error message.įor instance, instead of typing Daniel Chloe you mistyped Daniel Kloe as the lookup value. The lookup value is what the function is supposed to search for in the lookup table.
#Excel if then formula with vlookup how to#
Now let’s look at the circumstances under which the VLOOKUP function will generate an error and how to address these errors. At the left-hand side is also a small table that uses the VLOOKUP function to fetch the annual salary based on the name selected.Ĭauses of Excel VLOOKUP Not Working and Solutions Therefore, VLOOKUP #N/A error is Excel’s way of telling you that the lookup value is not found in the first column of the lookup table.Īt the right-hand side is a table containing employee information including employee name, occupation, hire date and salary details.
Let’s take the VLOOKUP function, for example, if you use it to look up a range and it can’t find what it is supposed to look for, it will give you the #N/A error message. This error is very common in performing lookup or statistical functions. In Excel, #N/A means The Value isn’t Available. Now, using the following VLOOKUP cases, let’s see how to deal with the most common VLOOKUP error message: #N/A Error. Just click hereto read a blow-by-blow guide to the VLOOKUP function. In this topic, I want to talk about some of the things that trip up the VLOOKUP function.īut first, if you are not familiar with this function, you should get to know what it means in plain English. Just like most useful things, it too has its side effects. We all know how powerful the VLOOKUP function is, yet it isn’t flawless. You can do so much cool stuff if you get to understand the nuts and bolts about the VLOOKUP function, how it works, why it fails to work sometimes, and how to fix any “Excel VLOOKUP not working” problem. If you manage to master this function, then you are qualified to brag a little about your Excel knowhow since you can solve many problems with it. Most of the Excel gods consider VLOOKUP as their favorite Excel function. Introduction to Excel VLOOKUP Not Working