Contextures

LOOKUP Function Examples

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

Video: LOOKUP Function

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.

Uses for LOOKUP

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

  1. Find last number in a column
  2. Find latest month with negative amount
  3. Convert student percentages to letter grades
  4. Find a shift number based on an incident time
  5. Find an account number based on exact match
  6. Get store number based on multiple criteria

Hlookup00

LOOKUP Syntax

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.

Vector Form

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

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.

Download the Sample File

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

More Tutorials

Compare Lookup Functions

VLOOKUP Function 

INDEX and MATCH Functions

VLOOKUP Number/Text Troubleshooting

HLOOKUP

 

Get weekly Excel tips from Debra

 

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: October 24, 2018 3:46 PM
Contextures RSS Feed