Study smart | Excel super-stars
Vlookup formula
Save time and make life easier by taking advantage of this powerful spreadsheet search tool
Words Traci Williams, excelace.co.uk
STATISTICS
1
Speed consideration
When using Vlookup for large databases, its efficiency declines. The average time taken for Vlookup to search a 10,000-row dataset and return values can range from 0.2 seconds to two seconds, depending on the complexity of the data and the lookup requirements.
2
Popularity
Vlookup is one of the most commonly used Excel functions. According to Microsoft, more than 60% of Excel users employ Vlookup in their data analysis tasks.
The Vlookup formula in Excel is a very powerful and widely used tool for searching for specific data in a table. Its main function is to look for a value within one column and return a corresponding value from another column within the same row.
Essentially, this formula enables two datasets to be linked together where the data matches.
Vlookup is short for ‘Vertical Lookup’ as it works with data in vertical columns. There is also a Hlookup formula (‘Horizontal Lookup’) that works with horizontal data.
How does the Vlookup formula work?
With the data below (left) we can use a Vlookup formula to populate the yellow cells by finding matches with the data in the green and blue blocks.
The first example will populate the customer code (in column C) by matching the customer name (from column B), with the customer (in column J) and returning the value on that row from column K.
You can either use the wizard or the syntax to enter the Vlookup formula as follows:
Select the cell where you want the formula result to appear, e.g. C4.
Wizard
From the Formulas ribbon, select Insert Function, then type in the word ‘Vlookup’, press Go, then press OK.
The wizard will appear with the four boxes (or ‘arguments’) to complete, as follows:
1
Lookup_value
The value to look for in the first column of the table (e.g. B4).
2
Table_array
The range of cells containing the data to be searched and returned (e.g. $J$4:$M$9). Include dollars to freeze this range.
3
Col_index_num
The column number containing the data to be returned (e.g. 2).
4
Range_lookup
This is an optional argument and if left blank will return an ‘exact’ match (i.e. FALSE). Could also enter TRUE for the nearest match. It is also possible to enter 1 (TRUE) or 0 (FALSE) instead of the text.
This would appear as:
The formula (and result) on the spreadsheet would appear as:
=Vlookup(B4,$J$4:$M$9,2,FALSE)
In this instance, the formula looks for the company name from column B (customer D).
It looks for this in the range $J$4:$M$9, where column J represents the first column in this range, and this is also the column where it will find a match for what it is searching for.
It can then find a match (on row 7) and will return the customer code from the second column in this range (column K), which is D1000 (i.e. cell K7).
As we have ‘frozen’ the range in this formula, we can simply copy and paste it into the entire column.
Syntax
This can also be typed directly into the cell (instead of using the wizard) and the syntax will appear beneath:
=Vlookup(lookup_value, table_array, col_index_num, [range_lookup])
Nearest match
This example will populate the discount column with the discount rate from the blue table.
In this instance, the discount is based on the quantity, but the blue table only includes four quantity values as they represent the four discount levels, meaning that everything between, say, 50-74 will attract the 5% discount.
This is a perfect example of where we will be unable to use FALSE for the Range_lookup as the exact matches for the quantities from column G cannot be found in column O.
We will need to use TRUE in this instance and it will find the nearest match in column O instead.
=Vlookup(G4,$O$4:$P$7,2,TRUE)
Using TRUE in Vlookup formula requires the range to be sorted in ascending order. When FALSE is used, the sort order is unimportant.
Dos and Don'ts of Vlookup
DO
- Ensure the lookup value exists in the first column: Vlookup must always be able to find the match in the first column of the range.
- Use FALSE for exact matches: When looking for specific data, usually text or codes, it’s best to use FALSE in the Range_lookup argument to return an exact match.
- Check the column number: Double-check the column index number (col_index_num) to ensure it corresponds to the column from which you want to retrieve data. This won’t update automatically if columns are inserted or deleted.
- Make sure the formats match: If you are using Vlookup with numbers, make sure the numbers are formatted the same in both lists and not mismatched as text and number.
DON'T
- Use Vlookup to return a column prior to the matching column: Vlookup can only search from left to right. It is not possible to return a column that sits before the column where a match will be found. Consider reorganising the columns or using a different formula such as Index-Match or Xlookup.
- Forget to freeze table references: If you’re copying the Vlookup formula across cells, remember to use $ for the ranges to prevent them from updating as the formula is copied.
- Include spaces at the end of text: The Vlookup will be unable to find a match if there are any spaces at the end of text. This is incredibly difficult to identify as the spaces are not always visible. Use ‘TRIM(B4)’ formula within the Vlookup formula to eliminate them.
Limitations
Despite its relative ease of use and popularity, Vlookup does have some limitations:
1
It only works from left to right: If you need to search for a value in a column that is to the right of the column you want to return data from, Vlookup won’t work. You’d need to rearrange the data or use a different formula (Index-Match or the newer Xlookup).
2
It only has two Range_lookup options: The only options available in Vlookup formula are TRUE (nearest match) or FALSE (exact match).
3
Case sensitivity: Vlookup is not case sensitive, which means it will treat ‘ABC’ and ‘abc’ as the same value. This can be problematic if your data relies on case-sensitive information.
4
Slower performance with large datasets: As the size of the table grows, Vlookup can become slower, especially when searching for exact matches.
5
Errors: If a match cannot be found, a Vlookup will simply return #N/A, which always looks unsightly. We would have to use an Iferror formula as well if we wanted it to return a specific value where a match could not be found.
If you encounter any of these limitations or need greater flexibility, Excel does have some alternative formulas, such as Index-Match and Xlookup. Merge Queries is also extremely powerful within Power Query.
Conclusion
The Vlookup formula is a useful and powerful tool that should definitely be in your toolkit when using Excel. It offers an efficient way to look up, match and retrieve information. Without it, this would be a manual and tedious task.
As long as the limitations are understood and considered, Vlookup can still be used highly effectively in many scenarios.
Be aware of the benefits to be gained by using alternatives such as Index-Match or Xlookup to significantly improve your data management and analysis skills in Excel.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is the purpose of the Col_index_num argument in Vlookup?
2
Which of the following is a limitation of Vlookup?
3
In the Vlookup formula, what does setting the Range_lookup argument to FALSE do?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).