Appendix A — vlookup

The VLOOKUP function in Excel is used to search for a value in the first column of a table range and return a value in the same row from a specified column. The function has four arguments:

  1. Lookup_value: This is the value to search for in the first column of the table array.

    • Example: If you’re trying to find information related to an employee named “John”, “John” would be your lookup_value.

  2. Table_array: This is the range of cells that contains the data you want to search through. It includes the column with the lookup value and the columns with the possible return values.

    • Example: If your data is in cells A1 to D10, your table_array would be A1:D10.

  3. Col_index_num: This is the column index number from which the matching value should be returned. The first column in the table_array is 1, the second column is 2, and so on.

    • Example: If you want to return data from the third column of the table_array, your col_index_num would be 3.

  4. Range_lookup (optional): This is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

    • If TRUE or omitted, VLOOKUP will look for the closest match to the lookup_value that is less than or equal to the lookup_value. For this to work correctly, the first column in the table_array should be sorted in ascending order.

    • If FALSE, VLOOKUP will look for an exact match. If there are multiple exact matches, the first one found will be returned. If there’s no exact match, an error will be returned. Examples:

  5. =VLOOKUP("John", A1:D10, 3, FALSE)

    • This formula will look for “John” in the first column of the range A1:D10. If it finds “John”, it will return the value in the same row from the third column (C). The FALSE indicates that you’re looking for an exact match.

  6. =VLOOKUP(100, A1:B10, 2, TRUE)

    • This formula will look for the closest value to 100 in the first column of the range A1:B10 that is less than or equal to 100. It will then return the corresponding value from the second column (B). Since TRUE is used, the first column should be sorted in ascending order for accurate results. Remember: