#N/A Error in VLOOKUP: Complete Troubleshooting Guide

#N/A

+VLOOKUP

VLOOKUP is one of Excel's most powerful lookup functions, but it's also one of the most frustrating when it returns #N/A. This error appears when VLOOKUP cannot find your lookup value in the first column of your table array, leaving you with a useless result instead of the data you need. The good news? #N/A errors are incredibly common and almost always fixable. Most often, they stem from simple issues: a typo in your lookup value, extra spaces in your data, mismatched data types, or searching in the wrong column. Sometimes your range_lookup parameter is set incorrectly, or your table array doesn't include the column you're trying to retrieve. In this guide, we'll walk through the most common causes of VLOOKUP #N/A errors and show you exactly how to diagnose and fix them. You'll be back on track in minutes.

Why VLOOKUP causes #N/A

Lookup value not found in first column

The lookup_value doesn't exist in the first column of your table_array. VLOOKUP searches only the leftmost column, so if the value isn't there, it returns #N/A. This is the most common cause.

=VLOOKUP("Smith",B2:D100,2,FALSE) where "Smith" is in column C, not column B (the first column of the range)

Column index number exceeds table width

The col_index_num is larger than the number of columns in your table_array. If you ask for column 5 but only have 3 columns in your range, VLOOKUP returns #N/A.

=VLOOKUP("John",A2:C100,5,FALSE) where the table_array only spans 3 columns (A, B, C) but you're requesting column 5

Approximate match with unsorted data or no match exists

When range_lookup is TRUE (approximate match), VLOOKUP requires the first column to be sorted in ascending order. If data is unsorted or the lookup_value is smaller than all values in the first column, #N/A results.

=VLOOKUP(5,A2:C100,2,TRUE) where column A contains [10, 20, 30] but no value ≤ 5 exists, or the column is sorted descending

Step-by-Step Solution

1Click on the cell displaying the #N/A error to select it and view the complete VLOOKUP formula in the formula bar
2Identify the lookup value (first argument) and manually search for it in the first column of your table array to confirm it exists—use Ctrl+F to search if the data is large
3Check that your table array range is correct and includes at least two columns; if the lookup column is not the leftmost column in your range, adjust the range or use INDEX/MATCH instead
4Verify the column index number (third argument) is not larger than the number of columns in your table array; count your columns and ensure the index matches
5Confirm the range_lookup argument (fourth argument) is set to FALSE or 0 for exact matches, or TRUE or 1 only if your first column is sorted in ascending order
6Check for leading or trailing spaces in either the lookup value or the table data using the TRIM function; wrap your lookup value as VLOOKUP(TRIM(A1),range,col,0)
7If the lookup value genuinely doesn't exist in your data, wrap the VLOOKUP in IFERROR to display a custom message: =IFERROR(VLOOKUP(A1,range,col,0),"Not Found")
8Press Ctrl+Shift+F9 to recalculate all formulas, then verify the error is resolved or replaced with your intended result

Concrete Example

Employee salary lookup in HR database

An HR manager uses VLOOKUP to retrieve employee salaries from a master employee table to populate a payroll report. The lookup table contains employee IDs in column A and salaries in column C.

Before (error)

=VLOOKUP(A2,MasterEmployees!A:C,3,FALSE)

After (fixed)

=IFERROR(VLOOKUP(TEXT(A2,"00000"),MasterEmployees!A:C,3,FALSE),"Employee ID not found")

Problem: The #N/A error appears because the employee ID in the payroll sheet doesn't exactly match the master list. The master list contains leading zeros (e.g., '00547') but the payroll sheet has the ID without zeros (e.g., '547'). VLOOKUP cannot find a match due to this mismatch.

Solution: Format the lookup value to match the master list format using TEXT() function to add leading zeros, or use IFERROR() to handle missing values gracefully and display a meaningful message instead of the error.

Prevention Tip

Always ensure your lookup value exists in the first column of your table array and that the range includes all data; use IFERROR(VLOOKUP(...), "Not Found") to handle missing values gracefully instead of displaying #N/A.

Free Tools to Fix Your Formulas

Use these free tools to avoid this error:

Excel Formula Generator

Describe what you want to calculate and get the Excel formula instantly

VLOOKUP Generator

Generate VLOOKUP formulas instantly by describing what you need in plain English

Excel Formula Explainer

Paste any Excel formula and get a clear, step-by-step explanation powered by AI. Understand complex formulas instantly.

Stop wasting time debugging #N/A errors in VLOOKUP—ElyxAI automatically identifies and fixes formula issues in seconds. Try it free today and get your spreadsheets working perfectly.