How To Create A Vlookup Function In Excel


What Is A Vlookup?

Great you have been told you need to do a Vlookup. What is a Vlookup? When should I use it?

A Vlookup function in Microsoft Excel is a glorified copy and paste tool. It looks for a matching piece of information finds it in a table and brings back information it found from a particular column. Imagine you have a massive set of data it goes on for ever, but you are only interested in a handful of columns from this data set. Use a Vlookup to make a smaller refined set of data and the data is still linked to the original so as updates occur you still have the latest information.

You could use a Vlookup for the purpose of making a smaller list and then using that list to create a PivotTable. More PivotTable Tips.

If you have an Invoice No, Order No, Product Id use the Vlookup function to bring back the description.

There are many types of lookup functions in Microsoft Excel, there is Lookup, Vlookup, Hlookup, Choose, Match and Index. The Vlookup is the simplest and therefore the most popular. The V in Vlookup stands for Vertical lookup. The H in Hlookup stands for horizontal lookup. Most times if your lookup value is presented horizontally you may have decided to select the data and perform a Copy/Paste Special Transpose then go ahead and do your Vlookup Function. If you only knew, you could have simply used the Hlookup function in Microsoft Excel.

When creating a Vlookup remember it will only look to the right. If you need it to look to the Left and the right of the Lookup Value use the Index function with the Match function together.

There are a few things to check before you can go crazy and start typing your Vlookup function. The table must be SORTED by the FIRST column in your Master List or table. Make sure you know the column number before you start typing your function. The structure for the Vlookup is as follows.

=Vlookup(Lookup value,Table,Col Number, False)

You can replace the table data with a Range Name, this will help to understand what the Vlookup is doing.

False or 0 means: Exact match to Lookup value

True or 1 Means: Nearest Lowest match to Lookup value (True is the default)

If you need to find the nearest highest, then the Vlookup is not the right tool for you. Try a different function like Index and Match together.

Watch this video for a Demo on “How To Create A Vlookup Function In Excel“?

Navigating Multiple Sheets In Excel Made More Efficient

If you find yourself working between multiple sheets you may find it frustrating to get quickly from one to the other. Pressing the navigation arrows on the bottom left will take for ever. You could use shortcut keys Ctrl Page Up, Ctrl Page Down but this isn’t any faster. Try the ultimate “Right Mouse Click” on the bottom left navigation arrows. You will be presented with a list of all your sheets simply select one item in the list initially then press the first letter of the sheet name you would like to get to. If it didn’t get the the one you wanted continue pressing the same letter. It will cycle through everything that starts with that letter. For a Visual explanation click on the video below.

Microsoft Office Small Group Training Sessions

AZ Solutions Pty Ltd delivers customized training courses here in Sydney – Australia. We come to you. All you need is a board room, PC’s for each student and a TV/ Projector with a HDMI connection cable. In our training sessions you are welcomed to bring examples of your work to class. We prefer it.

Call Now M 0414 417 059 visit www.azsolutions.com.au

About Customised Computer Courses Sydney - Sutherland Shire M 0414 417 059

On-Site customised computer training courses Sydney - Sutherland Shire. Excel, Word,Power Point, Access, Outlook, Visio, Publisher, Excel VBA e: Info@azsolutions.com.au m: 0414 417 059 www.azsolutions.com.au
This entry was posted in Excel and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s