VLOOKUP

The VLOOKUP function is designed to search for a specified value within a range and return data from a specified column within that range.

Example Scenario

Consider the following data table:

To locate the value 7 in the table and return the corresponding data from the second column, the VLOOKUP function can be utilized as follows:

=VLOOKUP(7, A2:E6, 2)

This formula searches the range A2:E6 for the index value 7:

Result

Upon locating the index value 7, the function returns the data from the second column of the specified range— "Windows XP":

Important Notes on Using the VLOOKUP Function

When employing the VLOOKUP function in ReoGrid, it's essential to consider the following points:

1. Sorting Requirement: The data within the first column of the search table, which is used as the lookup reference, must be sorted, either in ascending or descending order. This ensures the function's search logic operates correctly.
2. First Column Constraint: The search operation conducted by VLOOKUP is limited to the first column of the specified search table. It's not possible to direct the function to search through any other column.
3. Data Type Consistency: The first column can contain either numeric or textual data, but consistency in its sorting is mandatory for the function to perform accurately.
4. Handling Non-Existent Values: In cases where the specified index value doesn't exactly match any entry in the first column, VLOOKUP will return the closest, less-than match within the sorted data. For instance, if the lookup value is 8 and the sorted values in the first column are 5, 7, and 10, VLOOKUP will return the result corresponding to 7, as it's the nearest lesser value.

Handling Non-Existent Lookup Values

When the VLOOKUP function encounters a lookup value that doesn't exactly match any entry in the first column of the search range, it resorts to returning the closest lesser value. For instance, consider using the formula =VLOOKUP(4, A2:E6, 2) with the following data table:

In this scenario, the lookup value 4 is not present in the first column of the specified range (A2:E6). As a result, VLOOKUP seeks the nearest lesser value to 4 within the sorted first column, which in this case is 3.

The function thus returns the data corresponding to the index value 3, leading to the output "Windows 2000":

Exact match specified value

To force ReoGrid exact match specified index value, set VLOOKUP function 4th argument to TRUE, for example:

Result is:

Nothing returned from VLOOKUP function because specified index value was not found in search table.