You are here:

Excel/query on Vlookup function


I have a master data in spreadsheet wherein saving accounts numbers  and names of the employees to whom this account belong are mentioned. The account number is alpha numeric eg :1003L0010004431. I have another spreadsheet which contains the same account numbers as contained in the earlier spreadsheet and it contains the data about amount paid to those account holders.I want to use vlookup function to compile the data of amount paid to each account holder in sheet number 1. The problem is I am not able to apply the vlookup function it is showing error #NA

vishal merharwade,

#N/A means the value being looked up is not found in the other sheet.

If you think it should be there and it sounds like it should, then there is a possibility that the values are slightly different.  You may be looking up
in in the other sheet it is stored as
"1003L0010004431 "  

with a space on the end or perhaps a space at the beginning.  This is one of the most common errors especially if you are copying the data in one of the sheet from another application.  

Other problems can be because one value is stored as Text and the other is stored as number.  This is unlikely in your case because you said the values are alphanumeric (I assume all account numbers have at least one letter in them).  

I assume you know that the lookup range in the other sheet must have the account number (the value being looked up) on the left side of the range - the range must start with the account number (look up value) column for vlookup to work.

These are the things that are most common.  If you can't get it to work, if you send me the workbook with the two sheets I can take a look at it and try to diagnose the problem.  You would send it to

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.