30 Excel Functions in 30 Days: 16 – LOOKUP

Icon30DayYesterday, in the 30XL30D challenge, we had some fun with the REPT function, and created in-cell charts and a tally sheet. Now it’s Monday, and time to put those thinking caps back on!

For day 16 in the challenge, we’ll examine the LOOKUP function. It’s a close friend of VLOOKUP and HLOOKUP, but works in a slightly different way.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the LOOKUP information and examples, and if you have other tips or examples, please share them in the comments.

Function 16: LOOKUP

The LOOKUP function returns a value from a one-row or one-column range or from an array.

Lookup00

How Could You Use LOOKUP?

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

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, and 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

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: Find 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)

Lookup01

Example 2: Find Latest Month With 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)

Lookup02

Example 3: Convert student percentages to letter grades

Just as you did 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 percentage 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)

Lookup03

Download the LOOKUP Function File

To see the formulas used in today’s examples, you can download the LOOKUP function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the LOOKUP Video

To see a demonstration of the examples in the LOOKUP function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Get Last Number with Excel LOOKUP Function

_____________

You may also like...

4 Responses

  1. Luke Wisbey says:

    Debra, I’d say it’s worth extending the examples re: last value / last text by saying that these offer us useful approaches for error handling (pre IFERROR: XL2007)

    If for ex. we know the output of a given formula (our_formula) should always be a number (where valid) we can use LOOKUP to avoid the need for double evaluation, let’s:

    =LOOKUP(9.99E+307,CHOOSE({1,2},0,our_formula))

    similarly for Text strings

    =LOOKUP(REPT(“Z”,255),CHOOSE({1,2},””,our_formula))

    Note: 255 char limitation.

    This is especially useful where our_formula is inefficient (eg Array)
    There are notable limitations to the above of course – for example where the result of can be >1 data types such an approach is not really viable.

  2. Thanks Luke, very useful example.

  3. fanprogram says:

    If the first value in the lookup array/vector is bigger than the lookup value, the result is an #N/A error.
    This conclusion is wrong.try type array 4;2;3;1 in F1:F4. and in somewhere type =LOOKUP(2,F1:F4). then will get 2.Not #N/A error!

  1. May 14, 2013

    […] 30 Excel Functions in 30 Days: 16 Take a look at the topic LOOKUP Traps […]

Leave a Reply

Your email address will not be published. Required fields are marked *