LOOKUP Function Examples

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

Array Form

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

LOOKUP Traps

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.

Example 1: Last Number in Column

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)

Last Number in Column

Example 2: Latest Negative Amount

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)

Latest Negative Amount

Example 3: Convert Student Grades

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)

LOOKUP to Convert Student Grades

Example 4: Get Shift Number

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).

Get Shift Number

Example 5: Get Exact Match

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.

Account numbers and amounts

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.

result in B11

How It 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.

check if cells are equal to amount

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)

find the last 1

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"

result is No Match

Example 6: Multiple Criteria

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)

check two criteria

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)

results are 1 or zero

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.

Get the Sample File

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

LOOKUP Function Examples

Video: LOOKUP Function

Uses for LOOKUP

LOOKUP Syntax

Vector Form