1. lookup_value (required): This is the provided data or cell reference of the provided data that Excel uses to lookup a data in another cell of the same row. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. In the case of the example above, it will be the ID 101 or its relative cell reference e.g. A2.
2. lookup_value (required): This is the specified range of data used to search for a particular data in that range of data. The values in lookup_vector can be text, numbers, or logical values. In the case of the above example, it will be the ID column data range e.g. A2:A10.
Note that the values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
3. result_vector (optional): This is the range of columns that contains the data that Excel returns. The result_vector argument must be the same size as lookup_vector. In the case of the above example, it will be the PROCDUCT column data range e.g. B2:B10.
• When dealing with number columns, you must first sort the whole data either in ascending or descending order before typing the LOOKUP function else Excel returns an error message #N/A.
• If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
• If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.
Now let me use a more practical illustration to get put you through.
PRACTICAL ILLUSTRATION OF LOOKUP FUNCTION (VECTOR LOOKUP)
The figure below shows a spreadsheet that contains a list of workers and their ID. Use the LOOKUP function to find the worker with ID equal to JS003.
SOLUTION TO THE LOOKUP QUESTION
1. Prepare a worksheet as shown in the figure above.
2. Highlight the whole data (i.e. A3 to B7).
3. The ID column is already in descending order, therefore there is no need to resort it again.
4. Place your cell pointer in cell B10 and type the following formula:
=LOOKUP(A5,A3:A7,B3:B7) and press Enter.
Excel returns the name JUDE, the worker with the ID JS003 as shown in the figure below.