In this lesson we will learn how LOOKUP function works in Excel. The LOOKUP function finds the approximate match of a lookup value in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range.
=LOOKUP(lookup_value, lookup_range, [result_range])
- lookup_value : This is the value to find the approximate match
- lookup_range : This is the one-column or one-row range to find the approximate match of the lookup_value.
- result_range : This is optional argument. This is another one-column or one-row range to return the result. If this is not provided. LOOKUP function will return the result from lookup_range.
Let’s understand with a simple example. In below table we have colour and price for certain category of dresses.
In cell E2 we have added the below LOOKUP function.
Here A6 is the cell reference of the lookup_value and A2:A7 is the lookup_range. Since no result_range mentioned here, the lookup_value ‘White‘ has been returned from the lookup_range only.
Now in cell F2 we have added the below LOOKUP function.
Here E2 is the cell reference of the lookup_value, A2:A7 is the lookup_range and B2:B7 is the result_range. So the LOOKUP function here finds the approximate match of the lookup_value ‘White‘ in the lookup_range A2:A7 and returns the respective dress as ‘Coat‘ from the result_range B2:B7.
Now we will be trying to find the dress we can purchase with $31. To do that first let’s sort the table based on the Price column in Ascending order.
We have added the price value as 31 in cell E2 and in cell F2 we have added the below formula.
Here E2 is the cell reference of the lookup_value, C2:C7 is the lookup_range and B2:B7 is the result_range. So the LOOKUP function here finds the approximate match of the lookup_value 31 in the lookup_range C2:C7 which is 30.62 as the next value in Price column is 31.89 which is greater than 31 and returns the respective dress as ‘Coat‘ from the result_range B2:B7. Here since the exact lookup_value is not found, LOOKUP function matches the next smallest value in the lookup_range.
Now let’s find the colour and dress we can purchase with $100. Here in cell E2 we entered 100 and in cell F2 we have added the same formula as above to find dress. In cell G2 we have added the below formula to find the corresponding colour. Here the result_range has been updated as A2:A7
As you can see below here since the lookup_value is greater than all values in lookup_range C2:C7, LOOKUP function matches the last value and returned the respective colour and dress from the last row of the table.
- The lookup_range must be sorted in ascending order. Otherwise LOOKUP function will return #N/A error.
- When lookup_value is less than all (i.e. the first) value in lookup_range, LOOKUP function will return #N/A error.
- result_range must be the same size as lookup_range.
- LOOKUP function is not case-sensitive.
- LOOKUP function returns #REF! error if the relative references in the LOOKUP function is invalid. Like in case the cells have been deleted after the LOOKUP function is entered.