Share via

Vlookup #N/A error despite having verified the value exists

Anonymous
2022-11-11T09:48:09+00:00

Hi all,

I am trying to use VLookup in a sheet to find a value, based on someone's full name. The lookup is done in another Excel file, which shouldn't pose a problem. I have also verified that the values are the exact same in both Excel files, even checking the length.

I have used the following formula: =VLOOKUP(C8;'[My report.xlsx]Sheet1'!$A:$BD;53;FALSE)

C8 is where the cell is listed with the person's name. "My report.xlsx sheet1" in column 53 is the data I need to have. Unfortunately, it still returns with the #N/A error.

Could anyone please assist me with this?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2022-11-11T16:04:11+00:00

    Hi Max. I am an Excel user like you.

    If you are getting an #N/A, then Excel cannot find a match in the target sheet. I am going to start by taking a stab at a basic issue. Apologies if this is too basic, and we will continue to look for other issues.

    Having checked the names to confirm that they are the same, here is the pertinent question. You are looking up a name from C8 from your first sheet. What column is that name in in your My Report Sheet 1?

    If the name is NOT in Column A of your Sheet 1, then this is what you need to do. Your Lookup Table in the formula is $A:$BD. Unless the name you are looking up is in Column A on that sheet the formula will not work. Your Lookup Table must start with the column in which you are looking up the name. So, if the name is in Column B you would use $B:$BD and column # would be 52. If your name is in Column AA you would use $AA:$BD and Column BA would be column #27 in the table.

    Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

    Rich~M

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2022-11-14T07:34:23+00:00

    Hi Rich,

    No worries about the basic information. I'm a basic user and don't have much experience with Excel. Just learning on the go. The name I am looking for in the other sheet, is in another colum.

    I didn't know before that the actual value I look for has to be in the first column. A colleague showed this to me as well, and then the formula worked. Great to have the confirmation from you as well. This has been a valuable lesson.

    I changed it to the first column, and then it worked.

    3 people found this answer helpful.
    0 comments
  3. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2022-11-11T10:27:51+00:00

    If it's returning #N/A then there isn't a match (assuming the name is under 255 characters). If you locate the row in in Sheet1 column A where you think it should match and use that in a comparison formula like =C8=Sheet1!A100 does it return True or False?

    3 people found this answer helpful.
    0 comments
  4. Anonymous
    2022-11-14T07:13:32+00:00

    The strange thing is that I checked in the original file and the exact match was actually there. I did change the intended match from the other file to column A, which apparently helped. I'm entirely sure this would be necessary, but it helped nonetheless.

    1 person found this answer helpful.
    0 comments