Excel/vlookup/offset

Advertisement


Question
QUESTION: Dear Sir,

I am trying to saperate data from the database where the information of various BRANCH exist. In the attached file I need to saperate the data of ACHHAM branch but I have confusion while applying foumula. Seek your help in this regard.

Nawa Raj
Banking Officer

ANSWER: Nawa Raj -

I cannot see your attached file. However, I do have an explanation for the VLOOKUP() that might help clarify for you.

As an example:
Have you ever gone to a store that was having a tag sale - the color of the tag tells you the percentage off the item? You find an item you want to buy, look at the tag, and then look at one of the charts they've posted. Find your color, follow the dotted line to the right, and you find your percentage discount. Well, that's how VLookup works.

First thing you need is the chart. The chart needs to contain the item you're looking for [color] and any information about that item [discount]. In your case, your chart should have the name of the branch and their information, such as a phone number.

You already have this information in your spreadsheet, you just need to put them next to each other so they form a "chart" or "table." The item you are looking [branch] for should always be first, and MUST be in alphabetical order. If it isn't, it will bring back the wrong data!
To make it easy for the formula to find the information, select all of the information and name the selection by clicking up in the box at the upper left part of your screen (it probably has a cell name in it, like "A1"). Type in the word "table" or "data" or anything you want to call it, so long as there are no spaces. Hit enter, and you've Named your Range.

Now go to the first cell where you want to fill in the phone number when a branch name is entered. Here you are going to enter the following formula:

=VLOOKUP(Lookup_value,Table_array,_Col_index_num,Range_lookup)

The Lookup_value is the item you're looking at, the colored tag, the branch name.
The Table_array is the chart with the discounts; the table you created and named.
Col_index_num is the only confusing part. You're not interested in columns like A B C... you'll tell the column by number. In your table, branch name is column 1. If the next column is phone number, which is column 2. If phone number is the 3rd column, after branch and address, then use 3. And so on, for all other information.
Range_lookup is optional, and you don't need it here.

So, for example, your formula will be =VLOOKUP(C3,table,2) This means take whatever is in cell C3, find it in column 1 of my table, then bring back whatever you find next to it in column 2. Does this make sense?

Let me know if this helps, or if you still have specific questions - Andrea

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

overdue report
overdue report  

overdue report
overdue report  
QUESTION: Dear Sir,

thank you very much for the early response of my query.

I have little more confussion in this

I tried but it doesn't work. I think I am in mistake somewhere while inputing formula. Sorry yesterday I couldn't upload referance file today I have uploaded referance file. In the overdue report sheet there is data of different branches. I have to report each branch saperately i.e one branch should get information of that branch only and so on.. Once the formula is set i will insert the data daily in the master sheet and the formula should segregate branchwise in different sheet or file automatically.

Please provide me ref file after applying appropriate formula.

thanking you in advance

Nawa Raj

Answer
Nawa Raj -

The files you upload come to me as .pdf - I cannot input formulas and return them to you. But looking at your files, I'm not sure you want to use VLOOKUP(). If the purpose of your master sheet is to enter all your information on one page, but be able to print out separate reports for each branch, then you just need to insert page breaks between each branch -
Page Layout tab > Breaks group > Insert Page Break
And create Print Titles to print on every page -
Page Layout tab > Print Titles group > Rows to Repeat At Top

Is this what you are trying to accomplish? Otherwise, if you are looking to have someone construct the spreadsheet for you, you may have to find someone local.

- Andrea  
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


Andrea Lynch

Expertise

I am very familiar with teaching excel concepts, and formulas. I am not as capable with VBA questions.

Experience

I currently tutor in this area. I worked for four years developing curriculum and teaching Microsoft applications to adult audiences. I also previously volunteered for allexperts.com.

Education/Credentials
BA, English, Western Washington University Certificates in C Programming, ASP.NET, VisualBasic.NET, University of Washington Extension

Awards and Honors
Rated in the top ten instructors (national), New Horizons Computer Learning Center.

©2016 About.com. All rights reserved.