98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Topics » Vlookup in practice
Vlookup in practice
What is a Vlookup?
Vlookup is an Excel function used to extract data from a cell within an Excel table and copy it to a different cell.
The table can be in the same worksheet, or in a different worksheet or a different workbook. The worksheet, where the data is to be extracted from, needs its first column to be an index column. This index column contains one unique value for every item in the list.
For example the index column could be a list of part codes in a parts list, or a list of staff IDs in a staff list.
The index can be numerical or text based, so long as every item has its own index value. The vlookup formula uses a value typed into a chosen cell to search for a match in the index column in the data table. Once a match is found the vlookup is then told which column number to use to find the data to extract.
The data table for the vlookup can also be a range name. For example the data could be named Staff1 rather than specifying the cells $A$2:$D$30. It's much easier to then refer to the cell range as Staff1 in the vlookup formula rather than to the actual cell range and avoids the need for fixing the range with absolute references ($ signs) if the vlookup formula is to be copied.
How to create a Vlookup function
The vlookup function contains three parts, which are separated by commas, and looks something like this:
=VLOOKUP(INDEX,RANGE,COLUMN)
The index is the cell reference where we have typed the value we want to lookup, for example in cell A2.
The user then types the vlookup function into a different cell, for example in cell B2.
Vlookup then checks in the cells defined by range and looks for a match in the first column for the value typed in A2 to find which row the match is in. Then it locates the column number specified by column in the function to find the cell with the data to extract, and displays this data in cell B2.
For example, suppose Sheet1 has 2 columns of data called Country and Zone where Country is the unique index column.
COUNTRY ZONE
France Europe
Ghana Africa
Japan Asia
SA Africa
UK Europe
Then suppose in Sheet2 you want to look up the Zone for the country typed into cell A2
COUNTRY ZONE
Japan ?
Type this Vlookup formula next to Japan in B2 of Sheet2 to display the correct zone.
Here is the formula:
=VLOOKUP(A2,Sheet1!$A$2:$B$40,2,FALSE)
There is an optional fourth part (or argument) of the vlookup formula to do with the type of match that is made for the looked up cell, in this case Japan.
FALSE means an exact match is required otherwise #N/A is displayed for the zone.
TRUE means the vlookup formula searches for the closest lower match. TRUE is used where the index is a sorted numerical list.
For example, if you want to find a discount from a list of quantities in ascending order. If TRUE is used in the above example typing Jap will return Africa rather than Asia as Ghana is a the nearest lower country to Jap in the index. When using the TRUE argument the index must be in sorted ascending order. When using FALSE the index in the table does not have to be in sorted order.
Vlookups are sometimes used together with the FALSE argument to spot differences between two tables. If an item in one list does not appear in the first column of the other list then N/A appears.
Vlookups can also be used in reports where figures are extracted from a large table of data. For example, to see retail sales made for a particular week across several brands. Then by simply changing a week number on a sheet would display the sales report for that particular week number.
See the links below to see questions about the vlookup function and how to combine it with an IF function to change the #N/A message.
Related forum posts:
What does a VLOOKUP do?
What does a VLOOKUP do?
Vlookup formula
I'm currently using the below formula to look a list of numbers in another list of numbers and if found, output the minutes dialled for that number. Its looking for the numbers in column G in column A, and if it finds them outputting the minutes dialled which are in column D =IF(ISERROR(VLOOKUP(G13,$A$2:$D$386,1,FALSE)) ,0,VLOOKUP(G13,$A$2:$D$386,4,FALSE)) The problem I have is that for the numbers that are in column G, here are more than one entry in Column A, and the lookup is only f
How does VLOOKUP work?
How exactly does this work? I need this function in my everyday work so I would love some advice please.
vlookups and exact matches
how to find an exact match with vlookups
VlookUp and blank fields
Want to know how to get rid of the #N/A in a lookup so that if the data is not found the field is blank
vlookup results
where the result is a #n/a answer, how can I convert it through the isna function, perhaps using an =if function to something that can be summed at the total line
VLOOKUP to transfer data
How do I use VLOOKUP to transfer data from one workbook to another?
vlookup, pivottable
need to know a bit more about pivottable, formulas, vlookup, et
Related articles
How To Use Vlookup In Excel
Have you wanted to find out how to use Excel's vlookup function? This function is used to extract data from a range of cells and display the extracted data in a different cell. This article describes the elements which make up vlookup and then explains what vlookup does and describes how to use this function in clear easy to follow steps.
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
