Excel VLOOKUP Function

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.

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

VLOOKUP Example

Another Lookup & Reference Function