The Excel LOOKUP Function
Basic DescriptionThe Excel LOOKUP function 'looks up' a supplied value within a data set and returns the corresponding value from a second data set. If the function fails to find an exact match to the supplied 'lookup' value, it uses the largest value that is less than or equal to the supplied 'lookup' value. Because the function finds a 'nearest' match, it is not suitable for all lookups - if you require an exact match to your 'lookup' value, or an error message if this isn't found, consider using the Vlookup or the Hlookup Function. For the Lookup function to work correctly, it is necessary that the data to be searched be ordered (ascending numerically or alphabetically). If the data is not ordered, the function may return unpredictable results. The Excel Lookup function has two forms - the Vector form and the Array form. These are described individually below. The Vector Form of the Excel Lookup FunctionThe vector form of the Lookup function 'looks up' a value in a data vector (ie. a 1-dimensional list of data) and returns the corresponding value from a second data vector. The format of the function is :
LOOKUP( lookup_value, lookup_vector, result_vector )
Where the arguments are as follows :
Example of the Vector Form of the Lookup FunctionIn the spreadsheet below, cells A1-E3 relate to a variable interest rate, that is paid on a bank account. For balances of 0-$999.99, the interest rate is 3%, for balances of $1000-$1,999.99, the interest rate is 4%, etc. Cell A6 of the spreadsheet shows the balance of a specific bank account and cell B6 shows the vector form of the Excel Lookup function being used to look up the interest rate relating to this balance. 
The above Lookup function returns the interest rate of 5%, which is the correct interest rate to apply to a bank account with a balance of $45,000. In this example:
Note that the vector form of the Excel Lookup function can be used with any two sets of data that have one-on-one matching values. For example, two columns of data, two rows of data, or even a column and a row would work, as long as the lookup_vector was ordered (alphabetically or numerically), and the two data sets were the same length. The Array Form of the Excel Lookup FunctionThe array form of the Lookup function 'looks up' a supplied value in the first column or row of a supplied data array (ie. a 2-dimensional table of data) and returns the corresponding value from the last column or row of the array. The format of the function is :
LOOKUP( lookup_value, array )
Where the arguments are as follows :
Example of the Array Form of the Lookup FunctionIn the spreadsheet below, columns A-C list the grades that are assigned to examination marks lying within the ranges 0-44%, 45%-54%, etc. Cell F2 shows the score of 82% that was achieved by the student "Chris" in an examination. The Lookup function in cell G2 looks up this score in column A of the spreadsheet and returns the associated grade from column C. Note that, in this example, if the exact score of 82% is not found in column A, we want, instead, to use the nearest value below this score. 
The above Lookup function returns the grade for the score 82%, which is B. In this example:
Note: In this example, the Excel Lookup function recognised that it was searching for the lookup_value in the first column of the supplied array, and returning a value from the last column. However if the array is transposed so that the data runs across 3 rows, the function will recognise this, and will still return the correct values. Further Lookup Function ExamplesFurther examples of the Excel Lookup function can be found on the Microsoft Office website. Trouble ShootingIf you get an error from the Excel Lookup function this is likely to be the following : Common Error
Also, the following problem is encountered by some users: Your Excel Lookup function is returning the wrong value Possible Reason No. 1
This problem may arise if the data in the lookup_vector or in the first row or column of the supplied array, is not ordered, (alphabetically or in ascending numerical order). Solution No. 1
Try ordering the data by the lookup_vector or the first row or column of the supplied array. If this data reads down the spreadsheet columns, the ordering can be done using the Sort... option in the Data menu. If your Excel Lookup function still returns the wrong result, check that your supplied lookup range(s) contain ONLY the data you want to search. (eg. if you have used the whole of columns A-C as a supplied array, change this to omit your headings and also to omit any other data that might be further down the spreadsheet). Possible Reason No. 2
You may have unseen spaces at the start or end of either the value you are looking up, or in the cells of your lookup_vector or array. These spaces cause your lookup_value cell and the 'matching' cell in your lookup_vector or table_array to have slightly different content. Solution No. 2
Check the contents of the cells that you believe should match.
If there are unseen additional spaces in one or both of the lists, remove these using the
TRIM function:
Possible Reason No. 3
The contents of the cells that are being compared may have different data types.
For example, the cell containing your lookup_value may be stored as a number by Excel,
whereas the values in your table_array may be stored as text even though they look like numbers.
Solution No. 3
Force both sets of data to have the same type. For example, if you want both sets of values to be stored
as text, convert both sets of data to text, using Excel's Text To Columns tool:
The data in your selected cells should now be stored as text within Excel and so the Excel Lookup function should be able to 'look up' the matching value. Note that you could have chosen to convert the contents of your cells to Excel's 'general' type, by simply selecting the column data format General in the Text To Columns tool. Possible Reason No. 4
You may not actually require the function to return a closest match - you may only want a result if an exact match is found. In this case, the Excel Lookup function is not the correct function to use. Solution No. 4
Try using the Vlookup Function or the Hlookup Function instead! |
|||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||
|
|
Copyright © 2008-2010 ExcelFunctions.net |
