What is the LOOKUP Function?

The LOOKUP function in Excel is used to look up information in a row or column. It locates a value from the same position in the row or column as the starting value, so it’s really useful when dealing with structured tables where all the rows and columns contain similar data. There are two ways to write a LOOKUP formula in Excel depending on your needs. One form is called vector and the other is array.

LOOKUP Function Syntax & Arguments

There are two ways to use the LOOKUP function:

Vector

The vector form searches through one row or one column only. That range is called the vector. The value that’s returned is whatever is in the same position as the other selected data set. =LOOKUP(lookup_value, lookup_vector, [result_vector])

lookup_value is the value the function should look for within the vector. It can be a number, text, logical value, name, or reference. This argument is required. lookup_vector is the range. It can be a single row or a single column. The values in the vector must be in ascending order (e.g., 1, 2, 3 or A, B, C). This argument is required. result_vector is an optional range. If used, it must be the exact same size as lookup_vector.

Here are some more rules to remember when you use the vector form of the LOOKUP function:

If lookup_value is smaller than the smallest value in lookup_vector, Excel produces the error #N/A.If lookup_value can’t be found, the LOOKUP function matches the largest value in lookup_vector that’s less than or equal to lookup_value.

Array

The array form can search for a value in multiple rows and columns. It first locates the specified value in the first row or column of the selection and then returns the value of the same position in the last row or column. =LOOKUP(lookup_value, array)

lookup_value is the value the function should look for within the array. It can be a number, text, logical value, name, or reference. The values must be in ascending order (e.g., 1, 2, 3 or A, B, C). This argument is required.array is the range of cells that contain the value you’re comparing with lookup_value. This argument is required.

Also keep these rules in mind:

If lookup_value can’t be found, the largest value in array that’s less than or equal to lookup_value is used instead.If lookup_value is smaller than the largest value in the first row or column, the error #N/A is returned.If array includes more columns than rows, the LOOKUP function searches for lookup_value in the first row.If array includes more rows than column, the LOOKUP function searches for lookup_value in the first column.

LOOKUP Function Examples

Following are several examples of how to use LOOKUP in your formulas:

Use Lookup Vector to Search Table

Here’s an example of how to use the LOOKUP function when we need to check the price in a table that’s organized by part number. Since we know that the part numbers are listed in A2:A5 and the prices are in C2:C5, we can search for part number 1003 using those parameters.

Use Lookup Array to Search Table

The other way to use the LOOKUP function on the same set of data as the example above, is with an array. Instead of choosing two single columns, we’re selecting the entire table. However, since we need the price in this example, we’re stopping the selection at column C since the function will grab whatever value is found in the same position in the last column.

Find Closest Number in Table

This LOOKUP formula is cross-referencing the score in column A with the grading system in column D. The LOOKUP function sees where the score falls on the grading system, and then it looks up the grade in F2:F6 to know what to write next to the score. Since some of those values aren’t found in the table to the right, LOOKUP uses the next lowest value. This particular formula could also be written in array form like this: The results are the same since column D is the start of the selection and the end, which holds the grade, is column F.

Locate Last Number in List

The LOOKUP formula finds the last number in column A. Since 9.99999999999999E+307 is the biggest number you can have in an Excel worksheet, the formula will show the last number in the list, even if there are blank cells included in the range.

Locate Last Text Value in List

The example locates the last text value from column A. The REPT function is used here to repeat z to the maximum number that any text value can be, which is 255. Similar to the number example, this one simply identifies the last cell that contains text.

Use Table Data to Find Heading Values

This final example of the Excel LOOKUP function involves some things not described in this article, but it’s worth a look anyway so that you can see just how useful this function can be. The general idea here is that we’re determining the last entry in each row and then looking up the date in row 2 to know the last time we paid those bills.

Other Functions Like LOOKUP

LOOKUP is a fairly basic lookup/reference function. Others exist that are good for more advanced uses. VLOOKUP and HLOOKUP let you perform vertical or horizontal lookups and can specify whether to do an exact or approximate match. LOOKUP automatically returns the closest value if an exact match isn’t found. XLOOKUP is a similar, more advanced lookup function.