Master the VLOOKUP Function in Excel: 8 Tips and Tricks
VLOOKUP is one of the most popular functions in Excel, and for good reason. It’s easy to use and does something very useful. However, the VLOOKUP function is also limited and has tricky defaults. In this blog post, we will explore 11 tips and tricks that will help you master VLOOKUP. With these tips, you’ll be able to use VLOOKUP with confidence and get accurate results every time!
What Does The VLOOKUP Function Do?
The VLOOKUP function searches for a value in the first column of a table and returns the value in the same row from another column. The V in VLOOKUP stands for vertical, as in a vertical lookup through a table.
Where To Find The VLOOKUP Function?
Option 1:
You can find the VLOOKUP function in the Formulas tab of Excel under Lookup & Reference
Option 2:
You can use Formula Builder to walk you through the formula step by step.
Option 3:
You can manually type the formula into any cell.
The Formula
=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])
- lookup_value (required) – The value to look for in the first column of a table
- table_array (required) – The table from which to retrieve a value
- column_index_num (required) – The column in the table from which to retrieve a value
- range_lookup (optional) – TRUE = approximate match (default). FALSE = exact match
Let’s Take a Look at Some Real-World Examples
Excel Workbook
Video Walkthrough
Coming Soon
Example 1 – VLOOKUP Function with an Exact Match
In the first example, we will use VLOOKUP to find an exact match from a table of data. We have a table with information about stores across different cities. Let’s find the monthly sales for the city of New York.
For the formula, we start with =VLOOKUP(. The lookup_value is C7 for New York. You can either enter this directly into the formula (enclosed in parentheses) or reference a cell containing the lookup. I recommend a cell reference. The table_array should be a reference to a range. Make sure that the column you want to search across is the left most column of the range. The column_index is the number of columns to count right. This starts from the lest most column of the table_array. Finally, for the range_lookup select “FALSE” since we want an exact match.
Your turn: Use the workbook and VLOOKUP to follow the examples in blue and finish the practice formulas highlighted in green.
Example 2 – VLOOKUP Function with an Approximate Match
In the second example, we will use VLOOKUP to find an approximate match from a table of data. We have a table with information about stores across different cities. Let’s find what city has the closest sales to $820,000.
For the formula, we start with =VLOOKUP(. The lookup_value is C8 for $820,000. You can either enter this directly into the formula (enclosed in parentheses) or reference a cell containing the lookup. I recommend a cell reference. The table_array should be a reference to a range. Make sure that the column you want to search across is the first column in the range. In this case, we need to make monthly sales the lookup row, so we have to move the cities to the right of the range to allow the formula to work.
The column_index is the number of columns to count across. This starts from the first column of the table_array. Finally, for the range_lookup select “TRUE” since we want an approximate match.
Your turn: Use the workbook and VLOOKUP to follow the examples in blue and finish the practice formulas highlighted in green.
8 Tips and Tricks
1) There Might Be A Better Option
VLOOKUP is not always the best option to search for data. Check out our post on INDEX MATCH which is a great substitute for both VLOOKUP and HLOOKUP.
2) Don’t Look Left
VLOOKUP can’t look to the right of a reference, it can only look down. If you get an #N/A error you may have asked VLOOKUP to search in the wrong direction.
3) Are You Seeing Double?
VLOOKUP will only return the first reference that it comes across. With that in mind, make sure to remove any duplicates from your data set, or consider another formula like SUMIF should the duplicates be needed.
4) Check Your Formatting
The VLOOKUP function is extremely sensitive. Make sure to watch out for extra spaces, numbers formatted as text, references over 255 cells, or other items that don’t match perfectly.
5) Locking Down References
If you don’t want VLOOKUP to change when you copy it down or across, use absolute references. To make a reference absolute, put a $ before both the row and column reference like so: $A$14.
6) Wildcard
Lucky for us, VLOOKUP can use the wildcard characters * and ?. The * means “any number of characters” and the ? means “one character”.
7) Two-Way Lookup
Inside the VLOOKUP function, column_index_num is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the needed column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns.
8) Always Define Range_Lookup
In most cases, you will want an exact match so get into the habit of defining range_lookup even though it is optional.
Let’s Recap
The VLOOKUP function is one of the most popular functions in Excel. It’s easy to use and does something very useful. However, it is limited and has dangerous defaults. In this post, we talked about how to use VLOOKUP successfully. We went over some tips and tricks to help you avoid common mistakes. We also talked about when VLOOKUP might not be the best option.
If you have any questions, leave a comment below. And be sure to check out our other posts on Excel. Thanks for reading!
Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.