How to use the LOOKUP function
The Lookup function lets you find a value in a cell range and return a corresponding value on the same row.
Excel Function Syntax
LOOKUP(lookup_value, lookup_vector, [result_vector])
Arguments
| lookup_value | Required. The lookup value can be a number, text or a logical value. It can also be a cell reference. This argument can also handle multiple values but you need to enter it as an array function to make it work, there is an example later in this post. |
| lookup_vector | Required. This argument must be a single column or single row cell range. |
| result_vector | Required. The result vector is optional. If omitted the function returns the lookup value, if found. This argument must be a single column or single row cell range and have the same size as the lookup vector. |
Comments
The values in lookup_vector must be sorted ascending!
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.
Be careful with this function
The values in lookup_vector must be sorted ascending but that is not all. You may think you get an error if the lookup value is not found but that is not always the case.
Only if the lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns #N/A. See cell B4 and C4 in this animated picture.
So why does "Rome" return 2000? LOOKUP function can't find the lookup_value so it matches the largest value in lookup_vector that is less than or equal to lookup_value.
If Rome would have been in this sorted list, it would be between New York and Sydney. New York is less (alphabetically) than Rome but greater than London. The corresponding value to New York is returned, that value is 2000.
Anchorage is smaller than the smallest value "London" (alphabetically) and Lookup returns #N/A.
You really need to know what you are doing if you are going to use this function. I do recommend using this function with numerical ranges, see next example.
Find the largest value in lookup_vector that is less than or equal to lookup_value
This example uses a numerical value as a lookup value. If an exact match is not found the closest value is returned as long as it is smaller than the lookup value.
Formula in cell C2:
returns B.
The largest value that is smaller or equal to 1.15 is 1.09. The corresponding value to 1.09 is B.
Multiple values
You can concatenate values and search two columns, use the ampersand character "&" to concatenate values in an argument.
Formula in cell D2:
returns 40.
Lookups on multiple sheets
This formula allows you to specify a sheet name and a lookup value. Indirect function returns the cell reference depending on the value in cell B3.
Formula in cell D3:
Lookup within a lookup
This formula looks for a value in table1 and uses the corresponding value to do a lookup in table2.
Formula in cell C3:
You can see that the Product column exists in both these tables, the tables are in a way related. I have made more formulas for related tables, see these posts:
- Search for values in a related table
- Sum values in a related table
- Extract unique distinct values from a related table
- Working with three related tables
- Applying conditional formatting to related tables
Array formula
The first argument lookup_value allows you to enter not only a single value but multiple values. Lookup function returns an array that has the same size as the lookup_value, to demonstrate this I made this formula:
Array formula in cell C3:
How to enter an array formula
- Select a cell
- Copy/Paste the formula to formula bar
- Press and hold CTRL + SHIFT simultaneously
- Press Enter
If you did this right the formula begins with a curly bracket { and ends with a curly bracket }. See formula bar in the picture above.
Let me explain this array formula, LOOKUP(B3:B4,B7:B9,C7:C9) becomes LOOKUP({"A";"C"},{"A";"B";"C"},{10;20;30}) and returns this array {10;30}.
MAX function returns the largest value in the array {10;30} , 30 that is.
Download excel *.xlsx file
If you want to learn more about array formulas join Advanced excel course.
This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.
Articles with the 'LOOKUP' Function
The following 6 articles have formulas that contain the LOOKUP function.
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference [โฆ]
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found [โฆ]
Find last matching value in an unsorted list
I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about [โฆ]
The formula in cell D3 lets you get the last value in column B, it works fine with blank cells [โฆ]
Filter values based on numerical ranges
Eero S asks: Thank you *so* much for your detailed examples and actively replying to users! I have a problem, [โฆ]
Find last matching value in an unsorted table
DonW asks: Ok, you've shown it for regular ranges....how about within tables. I have a table similar to: ID Name [โฆ]
Functions in 'Lookup and reference'
The LOOKUP function function is one of many functions in the 'Lookup and reference' category.
How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula [โฆ]
The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) [โฆ]
How to use the CHOOSE function
The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above [โฆ]
How to use the COLUMN function
The COLUMN function returns the column number of the top left cell of a cell reference. If the argument is not [โฆ]
How to use the COLUMNS function
The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell [โฆ]
How to use the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments [โฆ]
How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value [โฆ]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, [โฆ]
Gets a value in a specific cell range based on a row and column number.
How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. [โฆ]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Identify the position of a value in an array.
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a [โฆ]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference [โฆ]
The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell [โฆ]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range [โฆ]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row [โฆ]
English
Spanish
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form