What is the Excel VLOOKUP Function?
The VLOOKUP function is used to lookup a value (exact/approximate) in the first column of a range, then returns the value in the right column in the same row.
The letter “V” in VLOOKUP stands for vertical, VLOOKUP function does a lookup in the first column of the range from the first row to the last row (forming a vertical direction). If the lookup value found (exact/approximate), the lookup process stop and looking to the right column to retrieve the desired value.
For the complete explanation of how to use VLOOKUP function read the article below:
- Complete Guide How to Do a VLOOKUP in EXCEL, the only article you need to know all about VLOOKUP formula, from beginner to advance.
VLOOKUP Syntax
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
lookup_value, required, the value you want to look up in the first column of table_array
table_array, required, the range of cells, the first column is used as a reference to look up the lookup_value.
col_index_num, required, the column number in the table_array from which to retrieve a value.
- Number 1 represents the first column, number 2 represents the second column and so on.
- VLOOKUP function return #VALUE! error if less than 1
- VLOOKUP function return #REF if greater than total column in table_array
range_lookup, optional, TRUE = approximate match (default). FALSE = exact match.
How to Use VLOOKUP Function in Excel
For example, there is a price table as shown below. What are the results of the VLOOKUP function in column F?
VLOOKUP Function #1
=VLOOKUP("caffe latte",A2:D13,2,FALSE)
Caffe latte is in column A row 2, the first column of range A2:D13. The col_index_num argument is 2 then VLOOKUP looks to the right at column number 2 (column B/ Tall size).
The value at row 2 column B is the return value. The result is 2.95
VLOOKUP Function #2
=VLOOKUP("caffe latte",A3:D14,2,FALSE)
Like the first VLOOKUP function, but the above formula returns #N/A error, why?
Pay attention to the table_array argument, there is a slight change, shifts 1 line down, but that’s the problem. table_array shifts so “caffe latte” is not in table_array, this is what causes #N/A error
VLOOKUP Function #3
=VLOOKUP("caffe latte",A2:D13,5,FALSE)
Like the first VLOOKUP function, but there is one different argument. Yes, it’s a col_index_num argument, table_array only has 4 column, 5 exceeds the available number of column, this is what causes the #REF! error.
VLOOKUP Function #4
=VLOOKUP("caffe latte",A2:D13,0,FALSE)
Like VLOOKUP function #3, the difference is in col_index_num argument. If the value is less than 1, it returns a #VALUE! Error.
VLOOKUP Function #5
=VLOOKUP(caffe latte,A2:D13,2,FALSE)
Can you find the difference with the VLOOKUP function #1? Caffe latte is text, so it must be enclosed with the double quotes, if not, VLOOKUP returns a #NAME error?
**
The complete results of the VLOOKUP function in column F can be seen in the image below
Read the article below for more detailed explanation of VLOOKUP error and solution
