Home / Software / Using the Vlookup Function in Excel

Using the Vlookup Function in Excel

Microsoft Excel has lot of common and domain specific inbuilt functions. They are smartly used to automate and reduce the processing time of complex calculation. VLookup is one of those special functions used in the process of searching and retrieving the target value. It is normally used in case of data which is organized in multiple columns or multi dimensional arrays. Array is list of values of same data type. VLookup function works vertically from top to bottom around the tabular data. It shows its strength while searching a value with spreadsheet having multiple columns where desired data lies somewhere in columns other than leftmost column. Left most column stores mainly unique values acting as key for the entire row. Values in leftmost column may be arranged in sorted or random order.

As per the documentation of Microsoft Excel Help, VLookup function search a value in the leftmost column of mentioned table and then returns back the output in the form of a value existing in the same row from a column in the table which is provided syntactically. Depending on the key column i.e. leftmost column, most eligible mapped value is returned. Sometime it happens that no searched value is matched in key column. In that case, VLookup cannot return any data from table rows. In such situation, some constant and variant string value is returned reflecting a kind of error in search process.

VLookup function accepts four arguments. Argument values filter out unwanted data and work on defined criteria. Hence arguments are also known as parameters. When the values are passed in combination with respective argument name, it is termed as named parameter. In that case, order of passing parameters can also be customized. First parameter name is “lookup_value”. Its value can be in the form of reference or simple text string data. It is passed to VLookup to be searched in leftmost column of the tabular data. Second parameter is “table_array”. It is name of the table which contains the searchable data. This parameter can be assigned a reference to a range or range name. Range name can be related to common database or list of values. Third parameter has name called “col_index_num”. It is important parameter because it decides the column number from which data will get returned to the user. Fourth parameter is called as “Range_lookup”. It contains logical value which impacts the searching method of VLookup function. By default “Range_lookup” value is passed as “True”. It means if its value is not passed then also its value is considered as “True”. To pass as “False”, we need to explicitly mention its value as “False” while invoking the VLookup function.

If the value of fourth parameter in VLookup function i.e. “Range_lookup” is “True”, leftmost column should have data sorted in ascending order. Otherwise VLookup function will not return correct output. If “Range_lookup” value is “False”, sorting order of leftmost column is not important. Third parameter i.e. “col_index_num” should be passed with proper values. If its value is less than 1, VLookup function returns “#VALUE!” value which is a form of error signal. If value of “col_index_num” is more than the total number of columns in the table, VLookup function returns “#REF!” value, yet another form of error value. Apart from these situations, if “Range_lookup” is passed as “False”, VLookup function tries to find exact match. While searching exact match, if it fails, it returns “#N/A” error value. If the value of first value parameter is smaller than smallest value of leftmost column, then also, it returns “#N/A” error value. So we can conclude that if VLookup function gets “Range_lookup” as “True”, it search for approximate nearest value, otherwise it goes for exactly matching value in key column.

About User Lin