Excel/vlookup

Advertisement


Question
QUESTION: I make a daily report for the customers having a unique numbers, I want that if I type a unique no. in cell 'A' the customer name should be auto fill in the cell 'B'

how I can do it?

ANSWER: Hi Sarfraz,

You can store the Unique Numbers and Names of your customers in a separate sheet or range, then use vlookup formula in your column B so that whenever you enter a unique number in cell A, excel would do a vlookup and display the name in column B.

Hope this helps.

Gulshan.

---------- FOLLOW-UP ----------

vlook
vlook  
QUESTION: I am trying to copy the 'vlook formula' in the  other cells in same column, the formula is copied but the range of source cells are changed as follows,

=VLOOKUP(B11,DATA!A11:B800,2,FALSE)
=VLOOKUP(B11,DATA!A12:B801,2,FALSE) (range is changed)

can you please help how to copy the same formula in all the cells in same column.

Thank you.

ANSWER: Hi Sarfraz,

You can type the formula as below:
=VLOOKUP(B11,DATA!$A$11:$B$800,2,FALSE)

when you copy it in the below cell, it should become:
=VLOOKUP(B12,DATE!$A$11:$B$800,2,FALSE)

by entering $, you are telling excel to keep that range constant.

hope this helps,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Thanx, Now I am facing a problem my sheet is getting heavy and day by day it is working slowly, like if I save the file it takes a time, courser is moving slowly etc. I want to make a work book for the source sheet (now  my source sheet is sheet 2) than what will be the formula to get the result from new work book?

Answer
Hi Sarfraz,

Your excel shouldn't have slowed down so quickly although vlookup is a heavy formula. There could be any of the below reasons:
1 you have copied the formula to the entire column, that takes up a lot of memory. You could copy the formula to just 200-500 or whatever rows necessary, then it won't be that slow.
2 check the config of your computer, maybe the RAM or processing speed is too low.


Revision:

If you are making a new workbook because of speed problem, that might not solve the issue. However, please see the solution given below:

If you want to make a new workbook for the source sheet, you need to update the links as follows:
=VLOOKUP(B12,<<your workbook name will come here>> DATE!$A$11:$B$800,2,FALSE)

You need to make sure that the source workbook and your file are in the same folder.
I'm expecting that you will have some difficulty in getting the formula to work. Please feel free to send me the excel files and I can create the formula and give it to you.
When sending mail, please remove confidential information from the file and add AllExperts to the subject of your mail. You can send the mail to - gulshanrajpurswani@gmail.com


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.