# MS Excel: LOOKUP Function (WS)

Learn how to use the Excel **LOOKUP function** with syntax and examples.

## Description

The Microsoft Excel **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:

## 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 Microsoft Excel **LOOKUP function** is:

LOOKUP( value, lookup_range, [result_range] )

### Parameters or Arguments

*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 optional. It 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*. If this parameter is omitted, the **LOOKUP function** will return the first column of data.

## 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

The **LOOKUP function** can be used in the following versions of Microsoft Excel:

- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

## Type of Excel Function

The **LOOKUP function** can be used in Microsoft Excel as the following type of function:

- Worksheet function (WS)

## Example (as Worksheet Function)

Let's look at some Excel LOOKUP function examples and explore how you would use the **LOOKUP function** as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel **LOOKUP examples** would return:

=LOOKUP(10251, A1:A6, B1:B6) | would return "Pears" |

=LOOKUP(10251, A1:A6) | would return 10251 |

=LOOKUP(10246, A1:A6, B1:B6) | would return #N/A |

=LOOKUP(10248, A1:A6, B1:B6) | would return "Apples" |

## LOOKUP Function (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 Microsoft Excel **LOOKUP function** is:

LOOKUP( value, array )

### Parameters or Arguments

*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

The **LOOKUP function** can be used in the following versions of Microsoft Excel:

- Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

## Type of Excel Function

The **LOOKUP function** can be used in Microsoft Excel as the following type of function:

- Worksheet function (WS)

## Example (as Worksheet Function)

Let's look at some Excel LOOKUP function examples and explore how you would use the **LOOKUP function** as a worksheet function in Microsoft Excel:

=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: In Microsoft Excel, I have a table of data in cells A2:D5. I've tried to create a simple LOOKUP to find CB2 in the data, but it always returns 0. What am I doing wrong?

Answer: Using the **LOOKUP function** can sometimes be a bit tricky so let's look at an example. Below we have a spreadsheet with the data that you described.

In cell F1, we've placed the following formula:

=LOOKUP("CB2",A2:A5,D2:D5)

And yes, even though CB2 exists in the data, the **LOOKUP function** returns 0.

Now, let's explain what is happening. At first, it looks like the function isn't finding CB2 in the list, but in fact, it is finding something else. Let's fill in the empty cells in D3:D5 to explain better.

If we place the values TEST1, TEST2, TEST3 in cells D3, D4, D5, respectively, we can see that the LOOKUP function is in fact returning the value TEST2. So we ask ourselves, when we are looking up CB2 in the data and CB2 exists in the data, why is it returning the value for CB19? Good question. The LOOKUP function assumes that the data in column A is sorted in **ascending** order.

If you look closer at column A, it is not in fact sorted in ascending order. If we quickly sorted column A, it would look like this:

Now the LOOKUP function correctly returns 3A when it is looking up CB2 in the data.

To avoid these sorting problems with your data, we recommend using VLOOKUP function in this case. Let's show you how we would do this. If we changed our formula below (but left our data in column A in the original sort order):

The following VLOOKUP formula would return the correct value of 3A.

=VLOOKUP("CB2",$A$2:$D$5,4,FALSE)

The VLOOKUP function does not require us to have the data sorted in ascending order since we used FALSE as the last parameter - which means that it is looking for an exact match.

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"})

Question: For the following function in Microsoft Excel:

=LOOKUP(M14,Sheet2!A2:A2240,Sheet2!B2:B2240)

How do I get it to return a blank cell if the LOOKUP value (M14) is blank?

Answer: To check for a blank value in cell M14, you can use the IF function and ISBLANK function as follows:

=IF(ISBLANK(M14),"",LOOKUP(M14,Sheet2!A2:A2240,Sheet2!B2:B2240))

Now if value in cell M14 is blank, the formula will return a blank. Otherwise it will perform the LOOKUP function as before.