Skip to main content

How to use VLOOKUP in Excel

The VLOOKUP function in Excel provides some advanced functionality that allows the user to combine information from one file, table or sheet into another file, table or sheet.

It is a relatively simple formula to use but it does have some elements that required understanding before it can be used with confidence.

A common scenario where Vlookup is often used is one where information relating to a specific product, person, or maybe an activity exists in two places and needs to be combined.




In this example, I'm going to use a pre-existing table to provide the values I'd like to 'lookup'.


And then I'm going to create a VLOOKUP function that returns the height of the person when supplied with one of the 3 names in cell F3

You can see there are 3 mandatory arguments and an additional optional argument.

The first argument, lookup_value, is the information that will be used to identify the associated match in the existing table, in this instance this will be the name which will be entered in cell F3. You can see this is highlighted when entered into the formula

The second argument, table_array, is usually a range that defines the boundaries of the existing information that will provide the value returned by the function. In this example it is b2:c5. The column on the left (column B) contains the names which will provide the link between the formula and the result and the column C contains the value that will be returned to the formula.


The third argument, col_index_num, is a whole number which identifies the number of columns to the right of the existing table that we would like to return to the formula. In this instance, the 'Height' is the second column of the existing table so the number entered is '2'.


The fourth and last argument, [range_lookup], is optional but can have an effect on the result of the formula. I would advise that this argument is always set to FALSE until you're more familiar with the formula and have some advanced needs. By setting this argument to FALSE you are ensuring that the Vlookup will always provide an exact match. 


Once this is added, the formula is completed. Pressing Enter on the current formula will result in an error as there is no value being provided to the function as the cell F3 is blank. This error is normal and expected.

When the contents of cell F3 is changed to something that exists in the existing table, i.e. one of the names in the cells B3, B4 or B5, the Vlookup formula will provide the associated height of the person. You can see from this example when the name Rod is entered into cell F3.

When the value in cell F3 is changed to Jane, the result of the VLOOKUP function automatically changes to the height associated with Jane, i.e. 





Labels:

Comments

Post a Comment

Popular posts from this blog

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...
Post a Comment
Read more

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work.  The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...
Post a Comment
Read more

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article)  Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...
5 comments
Read more