Excel Function Overview
Check here for a review of one of the many excel functions. I will add a new one every month. These functions are the key to becoming a power user in Excel.
Vlookup is a very powerful took used to convert a spreadsheet into a database. The vlookup function looks for a value in a table and returns information from another column in the table.
The vlookup function has 4 arguments which I will describe in detail in the following example.
=vlookup(lookup_value, table_array, column_index_number, range_lookup)
In this example the customer has run thousands of tests and they need to enter the test number and receive the test result. For this example the customer wants to know the result of test #4.
This argument is the is the value which the user wants to find. In our example the user is looking for the value 4 (for test #4).
This argument is the range which contains both the lookup_value as well as the return values. In this example the range would be "A2:B6".
Once the function finds the lookup_value it uses the column_index_number to determine which column to return to the user.
- If the value is less than 1 then VLOOKUP returns the #VALUE! error value.
- If the value is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
In our example we would like to return the 2nd column so therefore we would enter "2" for this argument.
The range_lookup is an optional True/False argument to determine if the user would like an exact match or a close match.
- If the value is TRUE or omitted then an approximate match will be returned to the user if an exact match cannot be found.
- If the value is FALSE then the function will only return EXACT matches. If no exact match exists then the function will return the error value #N/A.
In our example we only want exact matches therefore we would set the range_lookup to FALSE.
The entire function would be written as:
=VLOOKUP ( 4 , A2:B6 , 2 , FALSE )
The function would return the value 8.26
Please contact me if you have questions about how the vlookup function can be used in your application to help your company.