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.
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:
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 ----------
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 -
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.