You are here:

Excel/query on Vlookup function

Advertisement


Question
Sir,

I have a master data in spreadsheet wherein saving accounts numbers  and names of the employees to whom this account belong. 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

Answer
Hi Vishal,
I have a feeling that you are trying to sum up values using Vlookup. Vlookup doesn't work that way. It picks up the first record it finds and shows the corresponding value.
The reason for getting NA could be because there is some difference in your data - maybe extra spaces or something. I'm hoping both your data ranges are sorted in ascending or descending order. Vlookup works best with sorted data.

If you want to consolidate / sum up the data, try using Pivot tables. They could serve the purpose.

Hope this helps.
Gulshan.
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.