How to fix NA on excel sheet

Kamla Seerkissoon 0 Reputation points
2026-08-10T14:11:14.47+00:00

I'm using vlookup and it's picking up only few data in the column.

Microsoft 365 and Office | Excel | Other | Other
0 comments

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-08-10T14:11:33.6233333+00:00

    #N/A with VLOOKUP usually means Excel cannot find an exact match, even when the value appears to be there. For a case where only some rows are returned, check these in order:

    1. Use exact match in the formula.
      • Set the 4th argument to FALSE or 0.
      • Example: =VLOOKUP(A2,$A:$D,2,FALSE)
      • If that argument is TRUE or omitted, the first column must be sorted, otherwise Excel can return wrong results or #N/A.
    2. Make sure the lookup value exists in the first column of the lookup range.
      • VLOOKUP only searches the first column of the selected table array.
      • If the value is in column B but the range starts at column A, the lookup can fail.
    3. Check for text vs number mismatches.
      • A value can look the same but still not match if one side is stored as text and the other as a number.
      • In Excel, select the cells, then use Format Cells > Number to make both sides the same type.
      • If needed, apply the format to the full column, then use Data > Text to Columns > Finish.
    4. Remove extra spaces.
      • Leading or trailing spaces can cause #N/A.
      • Use TRIM to clean the lookup data.
      • Example: =VLOOKUP(D2,TRIM(A2:B7),2,FALSE)
    5. Check the return column index.
      • If col_index_num is larger than the number of columns in the selected range, Excel returns #REF!.
      • If columns were inserted or deleted, confirm the index still points to the correct return column.
    6. If the value truly may not exist, wrap the formula.
      • IFNA returns a custom result only for #N/A.
      • Example: =IFNA(VLOOKUP(A2,$A:$D,2,FALSE),"Not found")
      • IFERROR can also be used if a broader error handler is needed.

    If the formula is showing as text instead of calculating, confirm the cell is not being treated as text, then re-enter the formula.

    If available, XLOOKUP is the better option because it returns exact matches by default and is more flexible than VLOOKUP.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Kai-H 26,135 Reputation points Microsoft External Staff Moderator
    2026-08-11T08:04:38.6366667+00:00

    Hi, Kamla Seerkissoon

    If VLOOKUP works for the first few rows but returns #N/A farther down, the lookup range may be moving as the formula is copied. Hidden characters or wildcard symbols can also prevent valid matches.

    Here are some suggestions you can try:

    First, lock the lookup table with dollar signs. For example:

    =VLOOKUP(A2,$F$2:$G$500,2,FALSE)

    Press F4 after selecting F2:G500 in the formula to add the dollar signs, then fill the formula down again. Absolute references keep every row searching the same table. VLOOKUP best practices recommend this when filling formulas down.

    If the data was copied from a website, PDF, or another system, it may contain non-printing characters that TRIM does not remove. Clean the lookup column in a temporary column with:

    =CLEAN(SUBSTITUTE(F2,CHAR(160)," "))

    Copy the cleaned results, then use Paste Special > Values over the original lookup column.

    Also check whether lookup values contain * or ?. VLOOKUP treats these as wildcards. To search for the actual character, place ~ before it, such as ~* or ~?.

    Thank you for your patience in reading, I hope this information has been helpful to you. 

    If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.