As promised in my previous post, this time I will start by discussing the Vlookup Microsoft excel function. VLookup is one of the most commonly used excel functions in data analysis today.I would like to show you with examples when and where you could use VLookup in your day to day work. Microsoft® Excel is a simple and yet POWERFUL tool if you master it.
What is Excel’s VLookup Function?
VLookup is an excel function that is used to search for specific information located in a spreadsheet table. Vlook up is also known as the vertical lookup. The lookup looks for the needed information in the first column of the table range. There is also Hlookup. But because most of the look up needs involve Vlookup, this article will focus on Vlookup.
V-Lookup formula is written as follows:
VLookup (look up value, table range, column index (range lookup))
Below is an explanation of each of the terms:
- Look up value: This is the value we want to look for in the spreadsheet table.
- Table range: This is spreadsheet table range that we will need to search for the look up value. Here we identify the table range of cells where the Vlookup will search for the needed information.
- column index: This is the vertical column in the spreadsheet table range where to look for the needed information. Usually this is the first column
- Range lookup: This is an optional argument. This command instructs Vlookup function to search for exact match or approximate match of the needed information. If an exact match is required we use False or 0 range look up command. If no exact match is obtained excel returns # NA response. If an approximate match is needed then we use True or 1 range look up command; alternatively we can leave the range of lookup command blank. If the range lookup argument is true or omitted and exact match does not exist, excel bases the look up on the largest value in the first column that is less than the look up value. For approximate match range look up to work correctly the first column of the table range (the column of our interest for the search value) must be in an ascending numerical order.
Following below is an example of how to use Vlookup function.
Let us assume you are tasked with doing bank reconciliation and you need to come up with a list of outstanding checks at the end of a particular month say June 30, 2011.Since your firm uses positive pay, the bank provides your firm with a list of outstanding checks per the bank records. At the same time you have a file of outstanding checks per the firm’s internal records. The bank’s records of the outstanding checks does not reconcile to the firm’s internal records. Therefore, you will need to compare the two records and find out what are the reconciling items. This is how you solve the problem using Vlookup function.
Table A represents your firm’s internal records and table B represents the bank’s records. Follow the link below to access the Vlookup tables:
Further explanation follows below:
The Analysis:
1) As the difference of $31,715 shows there are checks in table B but not in table A
2) Also, there are checks included in table A but not in table B
3) Therefore, we will need to use Vlookup to analyse the cause of the differences
4) In this example I will look for checks in table B but not in table A
5) On your own look for the checks that are in table A but not in table B
6) The check number is our unique identifier so our query will be based on check #
How to write the Vlookup Function
Below is the format that we will use to construct the Vlookup function.
VLookup (look up value, table range, column index (range lookup))
Steps:
1) In cell H, 1 type equal sign then type v, a menu window will open with Vlookup function among others, double click it.
that now satisfies the first part of the formula: Vlookup
2) Our look up value is the check # in cell F1. We want to find out if this check # is also found in table A. So click cell F 1. Now we have our look up value
3) Next we select our table range and in this case it is A1:C 18 then press F4 to make the selection absolute
4) Next we select our table range and in this case it is A1:C 18 then press F4 to make the selection absolute
5) Our column index is the first column of our table (A) selection where to look for our look up value in this case we write 1
6) Now copy the formula down. You now have missing check numbers with N/A results. Now it is Easy!
Note1
Remember to separate each function with a comma.
Exercise
Now that you have become an expert do the following exercise on your own
On your own look for the checks that are in table A but not table B
Good Luck!
I like this website its a master peace ! Glad I discovered this on google. “Americans will put up with anything provided it doesn’t block traffic.” by Dan Rather.
I like this post, enjoyed this one regards for putting up.
Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you could do with some pics to drive the message home a bit, but other than that, this is wonderful blog. A fantastic read. I’ll definitely be back.
I conceive this website holds some really wonderful info for everyone. “As we grow oldthe beauty steals inward.” by Ralph Waldo Emerson.
Hello very cool blog!! Man .. Beautiful .. Superb .. I’ll bookmark your blog and take the feeds also…I am satisfied to seek out numerous helpful information here in the post, we want work out more techniques in this regard, thank you for sharing.
Thankx so much for this! I havent been this thrilled by a blog post for a long period of time! You’ve got it, whatever that means in blogging. Anyway, Youre definitely somebody that has something to say that people need to hear. Keep up the wonderful job. Keep on inspiring the people!
Some truly fantastic articles on this web site , thankyou for contribution.
You have mentioned very interesting points! ps decent site.
I have been looking for this information for quite some times. About seven hours of continuous finding, fortunately I got it in your article. I don’t understand why Google do not display this sort of resourceful sites in the first few pages. Usually the top websites are craps. Maybe it is time to try another search engine.
A person necessarily lend a hand to make seriously posts I would state. That is the first time I frequented your web page and so far? I surprised with the research you made to create this particular submit incredible. Excellent activity!
I really like your writing style, superb information, regards for posting :D. “I will show you fear in a handful of dust.” by T. S. Eliot.
You are my inspiration, I possess few web logs and sometimes run out from brand :). “Truth springs from argument amongst friends.” by David Hume.
I like this post, enjoyed this one thanks for putting up.
Very interesting points you have remarked, thanks for posting.
I truly appreciate this post. I have been looking everywhere for this! Thank goodness I found it on Bing. You’ve made my day! Thank you again!
Excellent writter, Thnkx for delivering the prestigious post. I found it useful. Best regards !!
I enjoy the efforts you have put in this, thanks for all the great posts .
I saw a lot of website but I conceive this one has got something special in it. “Human history becomes more and more a race between education and catastrophe.” by H. G. Wells.
hi!,I like your writing very much! proportion we communicate more about your article on AOL? I require an expert in this house to solve my problem. Maybe that is you! Having a look ahead to look you.
It laborious to seek out educated individuals on this matter, but you sound like you already know what you are speaking about! Thanks
I think this website holds some really fantastic information for everyone. “A man’s dreams are an index to his greatness.” by Zadok Rabinwitz.
I’ve been absent for some time, but now I remember why I used to love this blog. Thanks, I’ll try and check back more often. How frequently you update your website?
You may have not intended to do so, but I think you have managed to express the state of mind that a lot of people are in. The sense of wanting to help, but not knowing how or where, is something a lot of us are going through.
As soon as I found this web site I went on reddit to share some of the love with them.
I’ve been browsing online greater than three hours today, but I never found any interesting article like yours. It is beautiful worth sufficient for me. Personally, if all site owners and bloggers made good content as you did, the net will probably be much more helpful than ever before. “No one has the right to destroy another person’s belief by demanding empirical evidence.” by Ann Landers.
Howdy! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good gains. If you know of any please share. Many thanks!
Nice post. I was checking continuously this blog and I am impressed! Extremely helpful information specifically the last part 🙂 I care for such information a lot. I was looking for this particular info for a very long time. Thank you and best of luck.