Excel 2016: The VLOOKUP Function
Updated 2017.07.30
· Learn how to use the VLOOKUP function to look up data in a table.
The VLOOKUP (vertical lookup; Excel also has an HLookup—horizontal lookup) function searches for a value in the left-most column of a table (called the table_array in Excel). When it finds the value, it returns the value in the same row using the column_index_number to determine which column to select the value from.
Syntax for the VLOOKUP function is:
VLOOKUP(lookup_value, table_array, column_index_number, range_lookup)
· Lookup_value is the value to search for in the first column of the table_array.
· Table_array is a range (rectangle) of data whose first column is usually sorted in ascending order (there is an exception; see below).
· Column_index_number is the column number in table_array from which the matching value must be returned. The first column is 1.
· Range_lookup determines if you are looking for an exact match for the lookup_value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLOOKUP function will look for the largest value that is less than the lookup_value.
· If column_index_number is less than 1, the VLookup function will return the error #VALUE!.
· If column_index_number is greater than the number of columns in table_array, the VLookup function will return the error #REF!.
· If you enter FALSE for the range_lookup parameter and no exact match is found, then the VLookup function will return #N/A.
Use this Excel workbook for some examples.
Assume the following table is in A1:B12.
Score |
Grade |
0% |
F |
70% |
D |
77% |
C- |
80% |
C |
83% |
C+ |
85% |
B- |
87% |
B |
90% |
B+ |
93% |
A- |
95% |
A |
100% |
A |
This table can be used to look up letter grades if we know a percentage score.
If the score is 95%, we would look up the letter grade using the following formula:
=VLOOKUP(95%,$A$2:$B$12,2)
· The first argument is the value we want to look up: 95%. Note that this would most likely be a cell reference rather than a constant as in this example. See the green arrow below.
· The second argument is the table. Note:
· The headings are not included in the reference.
· An absolute cell reference is used to refer to the table. This is only necessary if the formula is going to be copied (as they usually are – if you're not going to copy the formula, there's probably no need to go to the trouble of setting up the lookup table).
· The third argument is the column that is to be used to retrieve the value ("A"). See the blue arrow below.
Again, assume we are using the same lookup table:
Score |
Grade |
0% |
F |
70% |
D |
77% |
C- |
80% |
C |
83% |
C+ |
85% |
B- |
87% |
B |
90% |
B+ |
93% |
A- |
95% |
A |
100% |
A |
In this example, the lookup_value is not an exact match for any of the value in the Score column.
If the score is 86%, we would look up the letter grade using the following formula:
=VLOOKUP(86%,$A$2:$B$12,2)
The arguments are similar to those in example 1, except that the 86% does not match a value in the Score column. In this case, Excel searches through the Score column until it finds the first value that is larger than 86% (this would be the 87% in row 8), then it backs up one row (to the 85% in row 7). Then it retrieves the value from the second column of the table (the Grade column): B-
If the numbers in the first column of your lookup table are not sorted, you can still do a lookup, but the value you are looking for must have an exact match in the table. It is also not necessary for the data in the first column of the lookup table to be numbers; it could also be text. Consider the following table:
Name |
Phone |
Larry |
555-0000 |
Moe |
555-1234 |
Curly |
555-9999 |
Ben |
555-3434 |
Adam |
555-2999 |
Hoss |
555-9222 |
Little Joe |
555-9090 |
John |
555-4567 |
Paul |
555-6789 |
George |
555-9876 |
Ringo |
555-1111 |
If the data in the first column of your lookup table isn't sorted, you must include the fourth argument to the VLOOKUP function, and its value must be FALSE, which means that it will look for an exact match. For example, to look up Ben's phone number (the name "Ben" is in E2), use the following formula:
=VLOOKUP(E2,$A$2:$B$12,2,FALSE)
Be careful with text. Spaces on the end of a name can mess everything up!