4 Reasons Your VLOOKUP is Giving #N/A
VLOOKUP stands for “vertical lookup” and its primary function is to find values in a column (vertical) and then return a corresponding value of another cell in that value’s row (horizontal). One example is if you are standing at the counter of a restaurant and you’re looking down the list of items and then looking over at the price when you find an item you are interested in ordering. Sometimes framing the syntax for a VLOOKUP might not be straightforward. This is when the dreaded #N/A result is returned even though you might be staring directly at the value you are searching for in the column.
Looking to become a certified Excel expert? Check out our Excel training class at ONLC!
What Does the #N/A Result Actually Mean?
#N/A is an abbreviation for “not available”, which means that the search cannot return a value or did not find a match. This result can be returned even though there are matches in the column if the VLOOKUP’s syntax is not formed correctly. There are several issues that could cause #N/A to be returned.
Common Reasons for #N/A in a VLOOKUP
Most of the time, #N/A is returned because the actual data inside the table cell is not what it appears to be in the spreadsheet. There are several possible causes for the disconnect between how the data is stored and what is appearing in the table cell.
1. Numbers are formatted as text
If the table cell is formatted as text, it will be treated as such even if it contains numbers, dates, or other data that aren’t normally treated as straight text. If you’re searching for specific numbers and the cells are formatted as text, then #N/A might be returned because an exact match can’t be found due to different data types. The best way to prevent this from happening is to highlight the table cells you want to search and then right-click them to format them with the appropriate data type.
Since column B is formatted as text, there should be double quotes around the search value or the column needs to be reformatted to a numeric data type.
2. Extra spaces in table lookup values
The most popular gotcha in spreadsheets, or just data in general, is leading or trailing spaces in the data item. Since VLOOKUP searches for an exact match, it will not find the matching cell if the data has spaces at the front or end of the value. To remedy this, make sure the spaces are removed from the cells in the column. This can be achieved through the use of the TRIM function, or the FIND & REPLACE method, or by utilizing a cell cleaner Excel add-in.
There is a trailing space on the value in cell B2, “2019 “, which caused the #N/A error.
3. Mistakes in the lookup_value argument of the VLOOKUP syntax
You would think this one is a no brainer, but it’s very easy to make typos and other mistakes in the lookup_value parameter when formulating the syntax of the VLOOKUP formula. Double-check to make sure that you have spelled and typed the lookup value correctly, it is in the correct case, and that it doesn’t contain leading or trailing spaces.
4. Lookup value not in the leftmost column of the table array
Make sure the value you are searching for is contained in the leftmost column designated in the table array of the second parameter of the VLOOKUP formula. Typo’s in the cell range will throw off the search. Be careful with this parameter. Also, if the column actually does contain the value, but it’s the wrong column, your formula will return incorrect values.
Sometimes an #N/A result is returned just because the VLOOKUP formula’s syntax is incorrect. One example could be that the lookup value is in the wrong order in the parameter list. Make sure that the value you are searching for is in the leftmost or first parameter in the formula. For example, VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
These are just 4 of the most common mistakes when using Excel’s VLOOKUP formula that will result in the dreaded #N/A error message. Excel is extremely powerful and includes a vast array of functionality.
Why not start your journey to becoming a certified Excel expert today? Check out our Excel training class.
