In Excel, the Lookup function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the Lookup function:
Syntax #1
In Syntax #1, the Lookup function searches for value in the lookup_range and returns the value in the result_range that is in the same position.
The syntax for the Lookup function is:
Lookup( value, lookup_range, result_range )
value is the value to search for in the lookup_range.
lookup_range is a single row or single column of data that is sorted in ascending order. The Lookup function searches for value in this range.
result_range is a single row or single column of data that is the same size as the lookup_range. The Lookup function searches for the value in the lookup_range and returns the value from the same position in the result_range.
Note:
If the Lookup function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the Lookup function will return #N/A.
If the values in the Lookup_range are not sorted in ascending order, the Lookup function will return the incorrect value.
Applies To:
- Excel 2007, Excel 2003, Excel XP, Excel 2000
For example:
Let's take a look at an example:
Based on the Excel spreadsheet above:
=Lookup(10251, A1:A21, B1:B21) would return "Tofu" =Lookup(10246, A1:A21, B1:B21) would return #N/A =Lookup(10248, B1:B21, A1:A21) would return "Queso Cabrales"
Syntax #2
In Syntax #2, the Lookup function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.
The syntax for the Lookup function is:
Lookup( value, array )
value is the value to search for in the array. The values must be in ascending order.
array is an array of values that contains both the values to search for and return.
Note:
If the Lookup can not find an exact match, it chooses the largest value in the array that is less than or equal to the value.
If the value is smaller than all of the values in the array, then the Lookup function will return #N/A.
If the values in the array are not sorted in ascending order, the Lookup function will return the incorrect value.
Applies To:
- Excel 2007, Excel 2003, Excel XP, Excel 2000
For example:
Let's take a look at an example:
=Lookup("T", {"s","t","u","v";10,11,12,13}) would return 11 =Lookup("Tech on the Net", {"s","t","u","v";10,11,12,13}) would return 11 =Lookup("t", {"s","t","u","v";"a","b","c","d"}) would return "b" =Lookup("r", {"s","t","u","v";"a","b","c","d"}) would return #N/A =Lookup(2, {1,2,3,4;511,512,513,514}) would return 512
Frequently Asked Questions
Question: I have the following Lookup formula:
=LOOKUP(C2,{"A","B","C","D","E","F","G","H","I","K","X","Z"},{"1","2","3","4","5","6","7","8","9","10","12","1"})
I also need to add zero to the lookup vector and result vector. How do I do this?
Answer: Using numbers in Excel can be tricky, as you can enter them either as numeric or text values. Because of this, there are 2 possible solutions.
Numeric Solution:
If you have entered your zero as a numeric value, then the following formula will work:
=LOOKUP(C2,{0,"A","B","C","D","E","F","G","H","I","K","X","Z"},{0,"1","2","3","4","5","6","7","8","9","10","12","1"})
Text Solution:
If you have entered your zero as a text value, then the following formula will work:
=LOOKUP(C2,{"0","A","B","C","D","E","F","G","H","I","K","X","Z"},{"0","1","2","3","4","5","6","7","8","9","10","12","1"})
