VLOOKUP function

Excel VLOOKUP Function is a powerful tool in Microsoft Excel that allows you to search for a specific value in a table, and then retrieve related information from that table. It’s like having a digital phone book for your data.


VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])


lookup_valueThe value you want to find in the first column of the table.
table_arrayThe range of cells that contains the data you want to search in. It should include the column with your lookup_value and the columns where you want to retrieve information.
col_index_numThe column number in the table_array from which you want to retrieve data. The first column is 1, the second column is 2, and so on.
[range_lookup](Optional) A logical value that can be either TRUE or FALSE. If TRUE (or omitted), VLOOKUP will find an approximate match. If FALSE, it will look for an exact match. (Optional means you can leave it out if you don’t need it.)

How to use

Let’s break down how to use the VLOOKUP function with an example:


Suppose you have a table of student names and their corresponding test scores. You want to find the score for a specific student, John, using the VLOOKUP function.

This formula will search for “John” in the first column (A) of the table (A1:B5). Since you want to retrieve the score, which is in the second column (B), you use 2 as the col_index_num. The last argument, FALSE, ensures an exact match. The function will return John’s test score.

85 (assuming John’s score is in cell B2)

Remember that VLOOKUP is incredibly versatile and can be used for various applications, from finding the prices of products in a database to looking up employee information in a personnel file.

Keep in mind that if the lookup_value is not found, VLOOKUP returns an error (#N/A). So, make sure your data is well-organized and the value you’re searching for is present in the table.

That’s the basic idea of the VLOOKUP function. It’s like your Excel search engine, helping you find and retrieve specific data from a big table.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.