Microsoft Excel allows you to organise large sets of data in a way that is simple and streamlined. You can organise just about anything from inventories and stock counts to customer details or an employee’s progress in learning and development. But a clean, pretty spreadsheet means nothing if you can’t go back and look up a particular value or embed a piece of information in another spreadsheet. This is where the VLOOKUP function comes in.
VLOOKUP can save you a lot of time that would otherwise be wasted on scrolling through rows and columns of data – and you don’t have to be an advanced Excel user to master it! You can learn Excel right here, right now with this mini Excel crash course on the VLOOKUP function.
But what is this function? Let’s find out how it works and how you can use it to free up more of your time this week.
What is VLOOKUP in Excel?
VLOOKUP is a function in Microsoft Excel that prompts Excel to search for a specific value in a column, or one that is approximate to that value, and input that value from another column in a specified row.
It stands for ‘Vertical Lookup’. So, if we take that at face value, the VLOOKUP function helps you to find a value that you want from a different column and present that value to you in the same row.
Sound confusing? Let’s unpack how the function helps you find a value in Excel, or enrol in our MS Excel Intermediate online course for guided learning by expert trainers in South Africa.
How the VLOOKUP function works
To find the information that you’re looking for, you will need to include the following four things in your function:
- The value that you want to find
- The range in which the value should we found, and the return value (in other words, the value you want presented back to you)
- The number of the column within the range that contains the return value
- ‘0’ or ‘FALSE’ for an exact match, or ‘1’ or ‘TRUE’ for an approximate match to the value you’re searching for
This is what your function should look like:
VLOOKUP([value]; [range]; [column number]; [0 or 1/FALSE or TRUE])
For example, your function could read: =VLOOKUP(F2,B2:D11,2,FALSE)
How to use the VLOOKUP function (and save a lot of time)
The VLOOKUP function helps you to find a value so you can save time searching for it manually in your spreadsheet. If you have a small data set, then you might get away with looking it up yourself. But if you have an inventory checklist of all the items in your warehouse with various SKU codes, stock counts and listing prices, your search for the price of that velvet swivel office chair with chrome legs might be more of a challenge.
So, how do you find a value in Microsoft Excel using the VLOOKUP function?
Step 1: organise the data
The function works from a left to right direction, so you need to ensure that the data is organised in a way that the function can read it correctly. The information that you want to find must be on the left of the data you want to return.
For example, if you’re searching for the price of the ‘velvet office chair’, the name of inventory items must be in a column somewhere to the left of the price of the inventory items.
Step 2: Tell the function what value to search for
Once the date is organised, tell Excel which value to look up. In this case, you would tell Excel that you want to focus on ‘velvet office chair’. To do this, you will need to type the function ‘=VLOOKUP(‘ and select the cell with the name of the item ‘velvet office chair’.
Step 3: Tell the function where to look for the value
Then you’ll need to select the collection of information in which Excel will need to search for the value chosen in step 2. This table of information must include the left most column that holds the value chosen in step 2, for example the name of the inventory items.
Step 4: Tell Excel which column to pull the data from
Next, you’ll need to tell Excel which column to find the value that you want presented back to you. Excel will then return the corresponding value in that specified column. For our example, this would be the price of the velvet office chair.
Step 5: Choose whether the value must be an exact or approximate match
Finally, you will need to tell Excel if you want an exact match to the value or an approximate one. This is where you will enter either ‘0’ or ‘FALSE’ for an exact match, or ‘1’ or ‘TRUE’ for an approximate one. If you tell Excel that you want an approximate match, it will search for the closest spelling or number to your value. This is helpful when you’re not sure about the lookup value.
Hopefully you can see just how powerful and handy the VLOOKUP function is. If you want to learn more tools and tips to increase productivity, browse our online Excel courses guided by our team of experts.
