The table_array in Figure 4-8 is the range A2:B21.Ĭol_index_num is the column number in table_array in which the matching value will be found and returned.
Table_array is the cell range in which Excel should search for the lookup_value. VLOOKUP always looks for the lookup_value in the first column of the array.
Lookup_value is the value to be found it can be a value (the number 14), a reference to a cell where the value appears (cell D2), or a text string (the part identification code GR083). The VLOOKUP() function has the following syntax: =VLOOKUP( lookup_value, table_array, col_index_num, range_lookup) If you don’t mind going to a little more trouble, you can avoid bogus matches and go beyond the two-column limit by using the VLOOKUP() function instead. In the case of the worksheet in Figure 4-8, if you hired a new sales rep who was assigned SalesID 21, but you didn’t enter the rep’s information into the worksheet, searching for the value would return Leiter, Catherine, which is incorrect. There’s also the possibility of getting an incorrect result: if LOOKUP() can’t find the lookup_value in the lookup_vector table, it matches the largest value that is less than or equal to lookup_value. The LOOKUP() function is somewhat limited in that the lookup_vector and result_vector can consist of only one row or column each. Typing 5 in cell C3 would return Cornwallis, Arthur, while typing 16 in cell C3 would return Ashby, Turner. In the worksheet in Figure 4-8, if you enter the formula =LOOKUP(C3,A2:A21,B2:B21) into a blank cell, it will take the value you enter into C3, locate the matching value in column A (looking from row 2 to 21), and return the value in column B that corresponds with the value found in column A. In other words, you’re telling the LOOKUP() function what to look for, where to look for it, and where to find the corresponding value you’re really interested in. If the employees’ names are stored in the range B2:B34, that’s the range you’d type here. Result_vector is the range within which to look, find, and return the corresponding value. If a list of employee IDs were stored in the range A2:A34, you would type A2:A34 in the lookup_vector spot. Lookup_vector is the range to search for the lookup_value. It could be an employee’s ID number, a Social Security number, or another unique identifier. Lookup_value is the cell (or value) to find in the table.