Functions
VLOOKUP
In its basic usage, the VLOOKUP function “looks up” data in a column and returns a value in a different column in the same row. This page contain basic VLOOKUP examples, getting a bit more complex with each example. The more recent and robust XLOOKUP (a dynamic array function) significantly updates the power of the LOOKUP function. See the Dynamic Array section of this website.
VLOOKUP: Simplified syntax:
| =VLOOKUP(LookUp Value,Where to Look,Location of value to be returned) |
Example 1:
The Sales Amount (D14) is the value to Lookup in the left column of the range CommTable. If an exact match is not found, it finds the next largest value and backs up one row. In this case the 1,234.00 is larger than the 1,000.00 value so Excel finds the next largest amount, 2,500.00 and backs up one row to the 1,000.00 amount and returns the the amount in the 2nd column of the range which is 5%.
In this example, a defined name is used rather that cell references.
The name CommTable = C4:D11
As such the formula in cell D15 would read:
=VLOOKUP(D14,CommTable,2)
To test the function, enter 4,567 in cell D14 and note the result in D15 is 10%.
To review:
- Amount must be in ascending order
- Excel looks for the number
- If not found, find the next highest number
- Then returns the number x places to the right
This exercise is looking up numbers. The rules vary a bit when looking up text. Also, remember, the lookup is always to the right. While there are work arounds to this, the Dynamic Array function XLOOKUP is much more robust and allows lookups in either direction.
Summary:
This page presents the VLOOKUP function using a basic scenario. More advanced examples are presented elsewhere on this site.
The technical syntax:
This is the Syntax as presented by Excel. The Simplified syntax at the top of the page is the Engish version of the Syntax.
| =VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup]) |
- Lookup_value is the value to lookup
- Table_array is the range of data where to look.
- The first column of table_array must contain the lookup values.
- The column_index_num argument is the column number of the value to retrieve. If left column in the table_array is column 1.
- Range_lookup controls match behavior. If range_lookup is TRUE, VLOOKUP will perform an approximate match. If range_lookup is FALSE, VLOOKUP will perform an exact match. This is typically used with text lookups which is presented in other exercises.
Video Test – Large Spreadsheets
Author: Jim Colville
Date: 7/18/1997 – Updated: 9/14/2019