subject: Excel Vlookup Formula top 3 Common Errors Explained [print this page] Excel vlookup formula is a widely used Excel function. It looks a little complicated to those who are new to it. Even those who are using this formula for years sometimes get annoyed with the errors which they get with this Excel vlookupformula. Excel Dummies sheds lights on three such most common errors and their possible solutions.
The main objective of Excel vlookup formula is to find out the specified data from the given table of data. It returns to the exact match of the value that has been asked for.
This error flashes before us when we ask the formula to return the specified match from a column which is not the part of our range in which we are looking for the value. We mistakenly specified a wrong column for the match to be returned from.
Suppose you have written the formula as =VLOOKUP(Salary,A2:B12,28,FALSE). You will definitely get the #REF! error for this formula. The problem is that we are commanding the formula to return a match at the 28th column of range whereas our range consists of only two columns. Therefore specifying a wrong match-return place gives this error.
Solution - A simple solution for this error is to rewrite the formula in a correct way by specifying right range and right column.
#N/A error in Excel vlookup formula
When you get the #N/A error you should understand that the result you have asked for does not exist. This error occurs commonly with any lookup formula in Excel. Again, this error tells us that the value, which we are trying to look for in our specified range, does not exist in that range.
Solution The #N/A error can be solved by using the Excel IF function and the ISERROR Excel functions.
#NAME error in Excel vlookup formula
The incorrect specification of the range for looking up the specified value can result in #NAME error. If you will refer to a name which does not exist, you will surely get this result. Sometimes you could have misspelled the name. It is also possible that you might be using labels in the formula without any label options in your setting menu.
Solution For a name that does not exist you can add that name by selecting Define in Name of the insert menu. You can look for any spelling mistakes and can type the correct spelling. If you want to use labels you should first select the label option in your settings menu so that you are allowed to used labels in your formula.
