How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.
VLOOKUP stands for vertical lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look vertically in the data array. HLOOKUP stands for horizontal lookup but that is for another post.
Formula in cell C3:
The VLOOKUP function uses lookup value AA-1611 in cell B3 and searches Table1 (cell range B6:E18) in the leftmost column. A matching value is found on row 16.
The third argument is which column you want to fetch the corresponding value from. In this example column 4 is entered and £30.00 is returned in cell C3.
The fourth and last argument lets you choose between approximate and exact match, in this case FALSE meaning EXACT match.
Excel function syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments
lookup_value - Value you want to look up
table_array - The range you want to use, remember that the VLOOKUP function always looks in the leftmost column in your specified range.
col_index_num - The column number which contains the return value
[range_lookup] - True or False (boolean value). True - approximate match, leftmost column must be sorted ascending. False - Exact match
Table of Contents
- Approximate match
- Related tables
- Dynamic arguments
- INDEX + MATCH
- VLOOKUP error
- VBA Example
- Using multiple conditions in VLOOKUP
- Download excel *.xlsm file
Approximate match
The fourth argument allows you to choose if you want APPROXIMATE or EXACT match. You want, in most cases, to use the EXACT match however the default value is APPROXIMATE match. So make sure you know what you are doing.
This example below shows you the difference between EXACT and APPROXIMATE match.
You must have your leftmost column sorted in an ascending order or you may get incorrect results.
Approximate match is useful if you want to find which group or range your value belongs to. The example above shows that value 158 is between 100 and 200 so the return value is 15.
Formula in cell C3:
Read more: Use VLOOKUP to calculate discount percentages
Related tables
A pivot table can't work with related tables (power pivots do) however the VLOOKUP function can quickly merge related tables so you then can analyze data in a pivot table.
These two tables share a data column (invoice), you can use the following formula to merge these two tables.
Formula in cell E3:
Copy formula in cell E3 and paste to E3:F15.
Dynamic arguments
This example shows you how to search multiple tables. The image below shows two excel tables, table10 and table20.
Formula in cell E3:
The INDIRECT function lets you use a cell value as the table_array argument, allowing you to search multiple tables.
INDEX + MATCH
The VLOOKUP function is that it can only look for a value in the leftmost column. If you don't have your values in the leftmost column you have two options, rearrange your table or use another method, shown below.
The INDEX and MATCH function allows you to search any column in a table and return a value in any column on the same row. This formula is so versatile that I actually prefer INDEX + MATCH over the VLOOKUP function.
Formula in cell C3:
The MATCH function lets you look for a value in a column and return the relative position of the found value in the array. AA-1611 is found on row 11 in cell range C6:C18
Read more: How to return multiple values using vlookup
VLOOKUP Error
The IFERROR function allows you to return a value if the VLOOKUP function returns an error, in this case NOT FOUND!
VBA Example
The following VBA code demonstrates how to use VLOOKUP in a macro.
The macro searches B6:B18 using the the value in cell C2 and displays the result in a messagebox.
VBA code Sub VLP() MsgBox Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:E18"), 4, False) End Sub
Download excel *.xlsm file
Articles with the 'VLOOKUP' Function
The following 6 articles have formulas that contain the VLOOKUP function.
How to use VLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
VLOOKUP of three columns to pull a single record
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Answer: Array formula in […]
Return multiple values if in range
The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]
Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]
Choose between two data sets to VLOOKUP
Question: How do I search a specific data set, I have two tables to choose from? Answer: Formula in cell C13: […]
Functions in 'Lookup and reference'
The VLOOKUP function function is one of many functions in the 'Lookup and reference' category.
How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]
The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) […]
How to use the CHOOSE function
The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above […]
How to use the COLUMN function
The COLUMN function returns the column number of the top left cell of a cell reference. If the argument is not […]
How to use the COLUMNS function
The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]
How to use the FILTER function
The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category […]
How to use the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments […]
How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value […]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]
Gets a value in a specific cell range based on a row and column number.
How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Identify the position of a value in an array.
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]
The SORT function lets you sort values from a cell range or array. It returns an array with a size […]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
How to use the UNIQUE function
The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and […]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
One Response to “How to use the VLOOKUP function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[…] https://www.get-digital-help.com/2017/07/11/everything-you-need-to-know-about-the-vlookup-function/ […]