How to Use LOOKUP Function in Excel (4 Suitable Examples)

There are a lot of functions in Excel that you can use to lookup for a value from a dataset. The LOOKUP function is one of them. The LOOKUP Function is categorized under Excel Lookup and Reference functions. If your problem is to find data from a range, this method will work at ease. In this tutorial, you will learn to use the LOOKUP function in Excel with suitable examples and proper illustrations.

excel lookup function vector form

The above screenshot is an overview of the article which represents a simplified application of the LOOKUP function in Excel. You’ll learn more about the dataset as well as the methods and functions under different criteria in the following sections of this article.


Download Practice Workbook

Download this practice workbook.


Introduction to the LOOKUP Function

Function Objective

The LOOKUP function looks up a value in the one-row or one-column range.

Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

Syntax 

The LOOKUP function is available in 2 formats. The Vector form and the Array form.

Syntax in Vector Form:

 =LOOKUP(lookup_value, lookup_vector, [result_vector]) 

Arguments Explanations (Vector Form)

Argument Required/Optional Explanations
lookup_value Required A value that LOOKUP explores for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
lookup_vector Required A range that includes only one row or one column. The values in lookup_vector can be text, numbers, or logical values.
[result_vector] Optional A range that incorporates only one row or column. The result_vector statement must be the same size as lookup_vector. It has to be the exact size.

Important Note to Remember: The values in lookup_vector must be put in ascending order: …, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the accurate value. Uppercase and lowercase text are identical.
[/wpsm_box]

Syntax in Array Form:

 = LOOKUP(lookup_value, array) 

Arguments Explanations (Array Form)

Argument Required/Optional Explanations
lookup_value Required A value that LOOKUP explores for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
array Required A range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.

Return Parameter (Vector and Array Forms)

In both formats, the LOOKUP function returns the output for the look-up value.


2 Ways to Use the LOOKUP Function in Excel

Now, there are two forms of the LOOKUP function. One is the Vector form and the other one is the Array form. We will discuss them in this section with simple examples. And we will tell you which one to choose. So, stay tuned.


1. Vector Form of LOOKUP Function in Excel

The vector form of the LOOKUP function will explore one row or one column of data for a particular value and then fetch the data from the exact position in another row or column.

excel lookup function vector form

You can see this from this example. Our lookup value was 1003. The LOOKUP function first searched for this value. Then, it returns the corresponding result from the same row.

Read More: 7 Types of Lookup You Can Use in Excel


2. Array Form of LOOKUP Function in Excel

The array format of LOOKUP peeks in the first row or column of an array for the particular value and returns a value from the same place in the last row or column of the array. We must use this form of LOOKUP when the values that we want to match are in the first row or column of the array.

We will recommend you to use HLOOKUP for horizontal lookup and VLOOKUP for vertical lookup instead of the Array form of LOOKUP.

When we use the array form of the LOOKUP function, the LOOKUP function acts based on the array dimensions. Your array looks taller that means it has more rows than columns. The LOOKUP will behave like the VLOOKUP function in that case.

If your array is wider, it will work like the HLOOKUP function. Wider means it has more columns than rows.

Take a look at this simple example.
Formula for Vertical Array:

=LOOKUP(E5,B5:C9)

Formula for Horizontal Array:

=LOOKUP(E6,C11:G12)

excel lookup function array form

Here, we have the same dataset. We placed the array differently. The first one is the vertical format and the second one is the horizontal format.

Read More: Excel LOOKUP vs VLOOKUP: With 3 Examples


4 Suitable Examples of LOOKUP Function in Excel

In the following sections, we will provide you with five examples. I hope it will clear your idea about the LOOKUP function in Excel. If you have already read previous sections, these examples will be easier to understand. I hope you will learn all of these and apply this knowledge to your worksheet.


1. Find Last Non-empty Cell Using the LOOKUP Function

Now, you can find the last non-empty cell from an entire column using the LOOKUP function in Excel. I think this formula will come in handy in a lot of situations.

Take a look at the following dataset:

Here, we have a simple dataset. Now type the following formula in Cell F4:

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

excel lookup function

As you can see, by using the LOOKUP function of Excel, we can easily find the last non-empty cell.


2. The LOOKUP Function to Find Latest Value in Excel

Sometimes, your dataset may contain the same data but with different values. It might be a date, price, age, etc. From a large dataset, you may want to find the latest data. In this case, you can use the LOOKUP function to get the latest entry.

Take a look at the following dataset:

The LOOKUP Function to Find Latest Value in Excel

This is a dataset of Ballon d’Or from the year 2009 to 2020. Our goal is to find the latest value for Ronaldo. Or you can say when was the last time Ronaldo won the Ballon d’Or.

Type the following formula in Cell G5:

=LOOKUP(2,1/(B5:B16=F5),C5:C16)

The LOOKUP Function to Find Latest Value in Excel

As you can see, we have successfully found the latest value using the LOOKUP function in Excel.


3. Find Grades Based on Marks Using the LOOKUP Function

Now, this example is an array example. It will show the vertical alignment. We will find a value based on data that is not present in the dataset. But, our LOOKUP function array form will find the nearest small value. We can use this to see grades.

Take a look at the following dataset:

Here, we have some marks and grades. On the right-hand side, we have a mark that is not in the dataset. Our goal is to find the grade based on that mark.

Type the following formula in Cell G5:

=LOOKUP(F5,B5:C11)

Find Grades Based on Marks Using the LOOKUP Function

 

Here, you can see the grade is A- because you have to get 70 to get the grade A. That’s why it chooses the nearest small value A-.

Read More: Advanced Excel Lookup Functions (9 Examples)


4. The LOOKUP Function Combined with IF to Show a Message If the Lookup Value is Empty

Now, what will happen if your lookup value is blank? If our lookup value is empty, it will show an error. We can use the LOOKUP function in an IF function to check whether your lookup value is blank or not. If it is empty, it will tell the user to give a value.

Take a look at the following dataset:

Here, we have some codes for some products and prices. Our goal is to find the price based on the Code. Now, type the following formula in Cell G5:

=IF(ISBLANK(F5),"Enter a code!",LOOKUP(F5,B5:B10,D5:D10))

excel LOOKUP function

As you can see, we have successfully found the price based on the code using the LOOKUP function.

Now, if you delete the code, it will tell you to enter a code.

excel LOOKUP function

Read More: How to Lookup Value from Another Sheet in Excel (3 Easy Methods)


💬 Things to Remember

Your lookup array must be in ascending order.

If an array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.

If the value of lookup_value is less than the smallest value in the first row or column (relying on the array dimensions), LOOKUP returns the #N/A error value.

When your  lookup_value is greater than all values in the range, the LOOKUP function matches the last value.

Result_vector must be the same size as lookup_vector.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the LOOKUP function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo