Excel LOOKUP function examples show how to get value from one-row or one-column range, or from array, with multiple rows and columns.

The LOOKUP function returns a value from a one-row or one-column range, or from an array, which can have multiple rows and columns

To see how the LOOKUP function works, watch this short video tutorial. This video uses the sample workbook from this tutorial.

The LOOKUP function can return a result, based on a lookup value, such as:

- Find last number in a column
- Find latest month with negative amount
- Convert student percentages to letter grades
- Find a shift number based on an incident time
- Find an account number based on exact match
- Get store number based on multiple criteria

The LOOKUP function has two syntax forms -- Vector and Array.

- With Vector form, it looks for a value in a specified column or row
- With Array form, it looks in the first row or column of an array.

The Vector form has the following syntax:

**LOOKUP(****lookup_value**,**lookup_vector**,**result_vector**)- lookup_value can be text, number, logical value, a name or a reference
- lookup_vector is a range with only one row or one column
- result_vector is a range with only one row or one column
- lookup_vector and result_vector must be the same size

The Array form has the following syntax:

**LOOKUP(****lookup_value**,**array**)- lookup_value can be text, number, logical value, a name or a reference
- searches based on the array dimensions:
- if there are more columns than rows, it searches in the first row
- if equal number, or more rows, it searches first column

- returns value from same position in last row/column

- The LOOKUP function doesn't have an option for Exact Match, which both VLOOKUP and HLOOKUP have. If the lookup value isn't found, it matches the largest value that is less than the lookup value.
- The lookup array or vector must be sorted in ascending order, or the result might be incorrect.
- If the first value in the lookup array/vector is bigger than the lookup value, the result is an #N/A error.

In the Array form, you can use the LOOKUP function to find the last number in a column.

The specifications in Excel's Help list 9.99999999999999E+307 as the largest number allowed to be typed into a cell. In this formula, that number is entered as the lookup value. Assuming that large number won't be found, the last number in column D is returned.

In this example, the numbers in column D do not have to be sorted, and there are text entries included in the column.

**=LOOKUP(9.99999999999999E+307,D:D)**

This example uses LOOKUP in its Vector form, with sales amounts in column D, and month names in column E. Things didn't go well for a few months, and there are negative amounts in the sales column.

To find the last month with a negative amount, this LOOKUP formula tests each sales amount to see if it's less than zero. Then, 1 is divided by that result, and returns either a 1 or a #DIV/0! error.

The lookup value is 2, which won't be found, so the last 1 is used, to return the month name from column E.

**=LOOKUP(2,1/(D2:D8<0),E2:E8)**

Just as you can do with the VLOOKUP formula, you can use LOOKUP, in its Vector form, to find the letter grade for a student's percentage score. With LOOKUP, the percentages do not have to be in the first column of the lookup table -- you can specify any column.

Here, the scores are in column D, sorted in ascending order, and letter grades are in column C, to the left of the lookup column.

**=LOOKUP(C10,D4:D8,C4:C8)**

This example uses LOOKUP in its Array form, with shift start times in column D, and shift numbers in column E. Thanks to UniMord for suggesting this formula.

When a factory machine breaks down, the incident start time is intered in column A, and this formula, in column B, calculates the shift number.

**=LOOKUP(A4,$D$4:$E$7)**

The formula finds an approximate match for the start time in the first column of the lookup table (D), and returns a value from that row, in the last column of the lookup table (E).

Although the LOOKUP function doesn't have an Exact Match option, you can build a formula that finds an exact match, if one exists. (Thanks to Alex Blakenburg for this example)

In the screen shot below, there is a list of account numbers, and the amount in each account.

The following LOOKUP formula, in cell B11, returns an account number, if there is an exact match for the amount entered in cell C11.

**=IFERROR(LOOKUP(2,1/($C$2:$C$8=C11), $B$2:$B$8), "No match")**

- When 19 is entered in C11, the result in B11 is account number 5.
- If a match is not found for the amount in C11, the result is "No match".

See the next section, for an explanation of how the formula works.

Here's how the formula works:

- In this formula, cells C2:C8 are tested, to see if they are equal to the amount in cell C11.
- That part of the formula was selected in the formula bar, and I pressed F9, to calculate that section. The 5th result is TRUE, and all the others are FALSE.
- Next, 1 is divided by each of those results. In Excel:
- TRUE = 1
- FALSE = 0

- When I calculate that section in the formula bar, the 5th result is 1 (1/1), and all the rest show a #DIV/0! error (1/0)
- The LOOKUP function won't find a 2, so it uses the last 1 -- the largest value that is less than the lookup value.
- The last 1 is in the 5th position, so the 5th ID number, in cells B2:B8, is returned.
- Finally, the IFERROR function returns the text string, "No Match" if the LOOKUP function returns an error. In the screen shot below, the amount 11 is not found, so the formula result is "No match"

In this example, two criteria are checked -- Fruit and Fresh. The store number for the last match is returned. (Thanks to Alex Blakenburg for this example)

This example uses LOOKUP in a structure that is similar to Example 5.

**=LOOKUP(2,1/((C2:C7=C10)*(D2:D7= D10)),B2:B7)**

- The Lookup value is 2, and cells are tested, to see if they are equal to the criteria cells.
- Then the test results are multiplied, and return 1 if both are TRUE (1x1=1)
- If either criterion test is FALSE, the result is 0 (any number x 0 = 0)
- The LOOKUP function won't find a 2, so it uses the last 1, in the 6th position. That is the largest value that is less than the lookup value.
- The 6th store number, in cells B2:B7, is returned.

To see the formulas used in these examples, download the
**LOOKUP
function sample workbook**. The file is zipped, and is
in Excel xlsx file format. There are no macros in the workbook

Last updated: October 24, 2018 3:46 PM

Contextures RSS Feed