EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel Excel Lookup Function
Secondary Sidebar
Excel Functions
  • Lookup and Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

Excel Lookup Function

By Jeevan A YJeevan A Y

LOOKUP Function in Excel

Excel LOOKUP Function (Table of Contents)

  • Introduction to LOOKUP Function in Excel
  • How to Use the LOOKUP Function in Excel?

Introduction to LOOKUP Function in Excel

The lookup function in excel is used to find the approximate match of any one row or one column as per selection criteria, and it returns the value from the corresponding any one row or one column. This works like Vlookup, but the Lookup function’s major advantage is that it considers both row and column to find the lookup range value.

In simple terms, this function searches the lookup value in a row or a column and gives a matching value in the same location from another cell or column. This is a built-in worksheet function in Excel. So it can be used as a formula in a worksheet. It can be found in Lookup & References under the Formula tab on the ribbon. It is present on all the versions of MS Excel.

Syntax:

LOOKUP Function Syntax

The LOOKUP function syntax has two types:

1. Vector

=LOOKUP(lookup-value, lookup-vector, result-vector)

  • Lookup Value: It is the value that we want to search in a range or array. It can be a text, number, reference, logical value or a name.
  • Lookup Vector: It is a single row or column containing data sorted in ascending order.

Result Vector: It is a single row or column having data and of the same size as the lookup vector.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)

2. Array

=LOOKUP(lookup_value, array)

  • Lookup _value: To search for a value in an array.
  • Array: It is the range of cells containing multiple rows and columns where you want to search the lookup value.

The Lookup function searches the lookup value in the Lookup vector and returns the value in the same position from the result vector. The function can return string, numeric, name depending upon the data type.

This function is used for finding prices of products from a data table, transactions from previous months/years, finding marks of students.

How to Use the LOOKUP Function in Excel?

We will now look at how to use the Lookup function with the help of various examples. You will get to know how to write the lookup formula to find the needed values for various criteria.

You can download this LOOKUP Function Excel Template here – LOOKUP Function Excel Template

Example #1

Starting with a basic example using the vector form of the lookup function. We have a sample data table of the seller and their products.

LOOKUP Function Example 1-1

Now, if we want to find out products sold by Kim, we will write the Lookup formula as below:

LOOKUP Function Example 1-2

Press Enter to see the result.

LOOKUP Function Example 1-3

We find out that Kim sells clothes.

Explanation of the formula:

=Lookup(B2,A2:A6,B2:B6)

Here B8 (Kim) is the lookup value as we want to know the product sold by her.

A2:A6 is the lookup_vector. We will search for the lookup value in this column.

B2:B6 is the result_vector. It is the column from where the function will return the value.

We wanted to find the product sold by Kim, so first, the function will search Kim in the A2:A6 range and then will return the value in the same position from B2:B6.

Example #2

We have s sample data showing ID, Items, and their Price.

LOOKUP Function Example 2-1

Suppose we want to find out the price for ID A04. For this, we will write the formula as below:

LOOKUP Function Example 2-2

Press Enter to get the result.

LOOKUP Function Example 2-3

The price of ID A04 is 70. Here the lookup value was ID A04; the lookup_vector was A2: A6, and the result_vector was C2: C6. For writing a correct formula, just follow the syntax that is prompted when you start writing the formula.

Example #3

Here we have a data table showing a product, price per unit and quantity.

LOOKUP Function Example 3-1

How do we find the number of apples from this table? We can write this formula for this:

LOOKUP Function Example 3-2

Press Enter to see the quantity of apples.

LOOKUP Function Example 3-3

Similarly, we can find out the quantities of other products like Orange.

LOOKUP Function Example 3-4

So with the above examples, you will develop an understanding of the Lookup formula using vector form. Now I will show you a Lookup array example.

Example #4

We have this sample data.

 Example 4-1

Now, if we want to search the name and pull matching price, how would we do it? Say I want to know the price for the products sold, Doug.

For this, I will write the lookup formula as below:

Example 4-1

Press Enter to see the result.

Example 4-2

Explanation of the formula:

=LOOKUP(B8, A1:C6)

Here theA10 is the lookup value as we wanted to know the price of the product sold by Doug. The array A1: A6 is the range of cells where we want to search B8. This is how we use the array form of the Lookup function.

Example #5

We can also know the last entry for the last entry in a data table’s non-blank column with the Lookup function. Taking the above data table, we can find out how this works.

 Example 5-1

The syntax is:

=Lookup(2,1/(column<>””), column)

To find the last entry, we will write the formula, as shown below:

 Example 5-2

Press Enter to see the results.

 Example 5-3

Similarly, we can find the last entries of the Product and Price columns as well.

 Example 5-4

Now the last entry in the Price column.

 Example 5-5

Example #6

As in our previous example, we found the last entry in a column using the Lookup function. We can also find the last entry in a row. We will write the formula for the same as shown below:

Example 6-1

Press Enter.

Example 6-2

The syntax is :

= Lookup(2,1/(Row<>””),Row)

Hence the last entry in Seller Row is Lisa. These were some of the most commonly used examples of the Lookup function In Excel.

Things to Remember

  • In the Vector form of the Lookup function, the values in Lookup_Vector should be in ascending order. If the values are numeric, they should be sorted from smallest to largest, and if they are in text form, they should be sorted from A to Z.
  • The Lookup Function is a case-sensitive function.
  • The Lookup_vector and Result_vector should be of the same size.
  • In the case of Array Lookup, if the number of columns is more than rows, the lookup function will search the value vertically.
  • The Lookup function will give N/A# Error if the lookup_ value is smaller than the smallest value in lookup_vector.
  • The same N/A# Error occurs if the lookup_vector is not in ascending order.
  • REF# Error occurs when the cell reference does not exist or the cell is deleted after the formula is applied.

Recommended Articles

This is a guide to the LOOKUP Function in Excel. Here we discuss how to use the LOOKUP function for various criteria in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VLOOKUP Tutorial in Excel
  2. VLOOKUP Examples in Excel
  3. Excel Alternatives to VLOOKUP
  4. IF VLOOKUP Formula in Excel
Popular Course in this category
MS Excel Training Bundle
  13 Online Courses |  100+ Hours |  Verifiable Certificates |  Lifetime Validity
4.5
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
Excel Advanced Training (16 Courses, 23+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Already registered !

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download LOOKUP Function Excel Template

EDUCBA

Download LOOKUP Function Excel Template

EDUCBA

डाउनलोड LOOKUP Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more