The Excel LOOKUP Function

Lookup, Vlookup and Hlookup Functions

The Excel Lookup, Vlookup and Hlookup functions are all related. The following table lists the differences between these functions:

Lookup-

Can lookup values vertically or horizonally;

The data to be searched (in the lookup row or column) must be ordered;

If an exact match is not found, the Lookup function will match the closest value below the lookup value.

Vlookup-

Performs a Vertical lookup;

The user can decide what the function should do if an exact match is not found - either return an error or match the closest value below the lookup value.

Hlookup-

Performs a Horizontal lookup;

The user can decide what the function should do if an exact match is not found - either return an error or match the closest value below the lookup value.

Basic Description

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 Function

The 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.

If the function can't find the exact lookup value, it will match to the closest value below the lookup value.

The syntax of the vector form of the Lookup function is:

LOOKUP( lookup_value, lookup_vector, [result_vector] )

Where the arguments are as follows:

lookup_value-The value that you want to look up.
lookup_vector-A 1-dimensional list of data, which you want to search.
[result_vector]-

An optional 1-dimensional list of data, from which you want to return a value.

If supplied, the [result_vector] must be the same length as the lookup_vector.

If the [result_vector] is omitted, the result is returned from the lookup_vector.


Example of the Vector Form of the Excel Lookup Function

In 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 that will apply to this balance.

Note that the exact value, 45,000 is not found in the lookup_vector, B1:E1, and so the function matches the closest value below 45,000 (i.e. 10,000) and returns the corresponding value from the [result_vector].

 ABCDE 
1Lower Balance$0.00$1,000.00$10,000.00$50,000.00 
2Upper Balance$999.99$9,999.99$49,999.99  
3Interest Rate3%4%5%6% 
4      
5Acct Balance:Interest Rate:   
6$45,000.00=LOOKUP( A7, B1:E1, B3:E3 )   - returns the value 5%

The Lookup function in cell B6 of the above spreadsheet 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 arrays of data that have one-to-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 is ordered (alphabetically or numerically), and the two data sets are the same length.



The Array Form of the Excel Lookup Function

The 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 syntax of the array form of the Lookup function is:

LOOKUP( lookup_value, array )

Where the arguments are as follows:

lookup_value-The value that you want to look up.
array-A 2-dimensional array (or table) of data, containing values to be searched in the first row or column and values to be returned in the last row or column.


Example of the Array Form of the Lookup Function

In 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, the exact score of 82% is not found in column A, and so the function will match against the closest value below this value (i.e. 75%).

 ABCDEFG 
1LowerUpperGrade NameScoreGrade 
20%44%F Chris82%=LOOKUP( F2, A2:C7 ) - returns the value "B"
345%54%E     
455%64%D     
565%74%C     
675%84%B     
785%100%A     

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.


Further Lookup Function Examples

Further examples of the Excel Lookup function can be found on the Microsoft Office website.


Excel Lookup Function Errors

If you get an error from the Excel Lookup function this is likely to be one of the following:

Common Errors
#N/A-

This error indicates that the Lookup function fails to find a closest match to the supplied lookup_value. This can occur if either:

  • The smallest value in the lookup_vector (or first column/row of the array) is greater than the supplied lookup_value.
  • The lookup_vector (or first column/row of the array) is not in ascending order.
#REF!-

Occurs if the formula is attempting to reference cells that do not exist.

This can be caused by either:

  • Cells being deleted after the Lookup function has been entered.
  • Relative references in the Lookup function, which become invalid when the function is copied to other cells.

Also, the following problem is encountered by some users:

Common Lookup Problem

The 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 (for the vector form of the function) or, if using the array form of the function, order by the first row or column of the supplied array. If your data is organised into columns rather than rows, 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 the supplied array, change this to omit any headings and 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 lookup_value, or in the cells of your lookup_vector or array. These spaces cause the lookup_value and the 'matching' cell in the lookup_vector or array to have slightly different content.

Solution No. 2

Click into the cells that you believe should match and check the contents. If there are additional spaces (or other unseen characters) in one or both of the lists, remove these. This can be done manually or, if you want to remove characters from an entire column of value, this can be done using the TRIM function as follows:

  1. Create a new column next to the one with the additional spaces in it.
  2. Use the Trim function in the new column, to get rid of extra spaces.
    (eg. if the original data is in cell A1, the function =TRIM(A1) removes any leading or trailing spaces).
  3. Copy the Trim function down the entire column of values.
  4. Copy the contents of the new column and paste the values only into the original column, using paste->special->values.
  5. Delete the column containing the Trim function.

Note that the Trim function removes trailing spaces from the start and end of a text string. However, if you have other unseen characters, you may need to carry out the above steps using the Excel Clean function instead of (or as well as) 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 lookup_vector or 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 numerical values, convert both sets of data to numbers, using Excel's Text To Columns tool:

  1. Use the mouse to select the cells you want to convert to text (this must not span more than one column)
  2. From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
  3. Make sure the Delimited option is selected and click next
  4. Make sure all the delimiter options are unselected and then click next again
  5. You should now be offered a selection of Column Data Formats. Select General and click the Finish button

Note that you could have chosen to convert the contents of your cells to text, by simply selecting the column data format Text in the Text To Columns tool.

Once you have ensured that your data has the correct type, check again that it is ordered correctly. This is important because Excel uses different ordering rules for text strings made up of numbers, compared to those used for numerical values.

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!