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:
The LOOKUP function has two syntax forms -- Vector and Array.
The Vector form has the following syntax:
The Array form has the following syntax:
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.
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.
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.
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.
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")
See the next section, for an explanation of how the formula works.
Here's how the formula works:
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.
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
Last updated: July 14, 2021 2:57 PM