Excel LOOKUP function with formula examples

Vertical Lookup formula - search in one-column range

Let's say, you have a list of sellers in column D (D2:D5) and the products they sold in column E (E2:E5). You are creating a dashboard where your users will enter the seller's name in B2 and you need a formula that would pull a corresponding product in B3. The task can be easily accomplished with this formula:

=LOOKUP(B2,D2:D5,E2:E5)

To better understand the arguments, please have a look at this screenshot:

Horizontal Lookup formula - search in one-row range

If your source data has a horizontal layout, i.e. the entries reside in rows rather than columns, then supply a one-row range in the lookup_vector and result_vector arguments, like this:

=LOOKUP(B2,E1:H1,E2:H2)

In the second part of this tutorial, you will find a few more Excel Lookup examples that solve more complex tasks. In the meantime, please remember the following simple facts that will help you bypass possible pitfalls and prevent common errors.

5 things you should know about vector form of Excel LOOKUP

1. Values in lookup_vector should be sorted in ascending order, i.e. from smallest to largest or from A to Z, otherwise your Excel Lookup formula may return an error or incorrect result. If you need to do lookup on unsorted data, then use either INDEX MATCH or OFFSET MATCH.
2. Lookup_vector and result_vector must be a one-row or one-column range of the same size.
3. The LOOKUP function in Excel is case-insensitive, it does not differentiate uppercase and lowercase text.
4. Excel LOOKUP works based on approximate match. More precisely, a Lookup formula searches for exact match first. If it cannot find the lookup value exactly, it looks up the next smallest value, i.e. the largest value in lookup_vector that is less than or equal to lookup_value.

   For example, if your lookup value is "5", the formula will search it first. If "5" is not found, it will search "4". If "4" is not found, it will search "3", and so on.

5. If lookup_value is smaller than the smallest value in lookup_vector, Excel LOOKUP returns the #N/A error.

4 things you should know about array form of Excel LOOKUP

1. If array has more rows than columns or the same number of columns and rows, a Lookup formula searches in the first column (horizontal lookup).
2. If array has more columns than rows, Excel LOOKUP searches in the first row (vertical lookup).
3. If a formula can't find the lookup value, it uses the largest value in the array that is less than or equal to lookup_value.
4. If the lookup value is smaller than the smallest value in the first column or row of the array (depending on the array dimensions), a Lookup formula returns the #N/A error.

Important note! The functionality of the Excel LOOKUP array form is limited and we don't recommend using it. Instead, you can use the VLOOKUP or HLOOKUP function, which are the improved versions to do vertical and horizontal lookup, respectively.

Look up a value in the last non-blank cell in a column

If you have a column of dynamically populated data, you may want to pick the most recently added entry, i.e. get the last non-empty cell in a column. For this, use this generic formula:

In the above formula, all arguments except for the column reference are constants. So, to retrieve the last value in a specific column, you just need to supply the corresponding column reference. For example, to extract the value of the last non-blank cell in column A, use this formula:

=LOOKUP(2, 1/(A:A<>""), A:A)

To get the last value from other columns, modify the column references like shown in the screenshot below - first reference is the column to be checked for blank/non-blank cells, and the second reference is the column to return the value from:

Look up a value in the last non-blank cell in a row

If your source data is laid out in rows rather than columns, you can get the value of the last non-empty cell using this formula:

In fact, this formula is nothing else but a slight modification of the previous formula, with the only difference that you use the row reference instead of the column reference.

For example, to get the value of the last non-empty cell in row 1, use this formula:

=LOOKUP(2, 1/(1:1<>""), 1:1)

The following screenshot shows the result:

Get a value associated with the last entry in a row

With just a little creativity, the above formula can easily be customized for solving other similar tasks. For instance, it can be used to get a value associated with the last instance of a specific value in a row. This may sound a bit obscure, but the following example will make things easier to understand.

Assuming you have a summary table where column A contains the seller names and subsequent columns contain data of some kind for each month. In this example, a cell contain "yes" if a given seller has closed at least one deal in a given month. Our goal is to get a month associated with the last "yes" entry in a row.

The task can be solved by using the following LOOKUP formula:

=LOOKUP(2, 1/(B2:H2="yes"), $B$1:$H$1)

The formula's logic is basically the same as described in the first example. The difference is that you use the "equal to" operator ("=") instead of "not equal to" ("<>") and operate on rows instead of columns.

The following screenshot demonstrates a result:

Lookup as alternative to nested IFs

In all of the Lookup formulas we've discussed so far, the lookup_vector and result_vector arguments were represented by range references. However, the syntax of the Excel LOOKUP function allows supplying the vectors in the form of a vertical array constant, which enables you to replicate the functionality of nested IF with a more compact and easy-to-read formula.

Let's say, you have a list of abbreviations in column A and you want to replace them with full names, where "C" stands for "Completed", "D" is "Development, and "T" is "Testing". The task can be accomplished with the following nested IF function:

=IF(A2="c", "Completed", IF(A2="d", "Development", IF(A2="t", "Testing", "")))

Or, by using this Lookup formula:

=LOOKUP(A2, {"c";"d";"t"}, {"Completed";"Development";"Testing"})

As shown in the screenshot below, both formulas yield identical results:

If you are pulling values from a lookup table, then you can embed a Vlookup function in the lookup_value argument to retrieve a match.

Assuming the lookup value is in cell E2, the lookup table is A2:C7, and the column of interest ("Status") is the 3^rd column in the lookup table, the following formula does the job:

=LOOKUP(VLOOKUP(E2, $A$2:$C$7, 3, FALSE), {"c";"d";"t"}, {"Completed";"Development";"Testing"})

As demonstrated in the screenshot below, the formula retrieves the project status from the lookup table and replaces an abbreviation with the corresponding word:

I hope these examples have shed some light on how the LOOKUP function works. To better understand the formulas, you are welcome to download these Excel Lookup examples. In the next tutorial, we will discuss a few other ways to do lookup in Excel and explain which Lookup formula is best to be used in which situation. I thank you for reading and hope to see you on our blog next week!