Vlookup Explained

VLOOKUP - Introduction

The first example explains how the Vlookup function works.

VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

The Vlookup function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.

lookup_value - is the value to be found in the first column of the table. This is one of the downsides using vlookup. I will show you how to look for a value in any column using INDEX and MATCH later in this post.

table_array - is a table of text, numbers or logical values. Table_array can be a cell reference or a range name. By modifying the table_array using an array formula we can use multiple conditions to create a "filtered" table_array. You can also use a table name, I´ll show you this later in this post.

col_index_num - is the column number in table_Array from which the matching value schould be returned.

range_lookup - is a logical value: Find the closest match in first column sorted in ascending order (TRUE) or find an exact match (FALSE)

Another downside with VLOOKUP is that it can only return the first value even if there are multiple matches, I made post a few years ago about this:

How to return multiple values using vlookup in excel

VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

=VLOOKUP(C2, A8:E17, 3, FALSE)

This formula matches the value in cell C2 ("A") to the first column in cell range A8:E17. The second argument is the column number and the function returns the value from that column and in the same row. The last argument FALSE instructs the formula to look for an exact match.

=VLOOKUP(C2, A8:E17, 3, FALSE)

becomes

=VLOOKUP("A", {"A", "Australia", "Atlantic Corporation", "Birdie", "Schneider";"B", "North America", "Uplink Corporation", "Amshula", "Canty";"C", "Asia", "Omni Consumer Products", "Jaycee", "Martin";"D", "Europe", "Galaxy Corp", "Tracy", "Tuck";"A", "South America", "The New Firm", "Tory", "Byrnes";"B", "Australia", "ZiffCorp", "Santos", "Cowart";"C", "North America", "Minuteman Cafe", "Gen", "Lindgren";"D", "Asia", "Demo Company", "Edwin", "Shinn";"E", "Europe", "Western Gas & Electric", "Allayna", "Egan";"F", "South America", "Trans United Airways", "Ervin", "Hennessey"}, 3, FALSE)

and returns Atlantic Corporation.