• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

LOOKUP in Excel

Home » Excel » Blog » Lookup & Reference Functions in Excel » LOOKUP in Excel

LOOKUP Function In Excel

Excel LOOKUP Function (Table of Contents)

  • LOOKUP In Excel
  • LOOKUP Formula in Excel
  • How to Use the LOOKUP Function in Excel?

LOOKUP In Excel

LOOKUP function is an inbuilt function categorized under lookup and reference functions.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

When you need to search for a specific data in one row or one column, will use lookup function, its main purpose is to Look up a value in a one-column or row range or an array (two-dimensional array (table) of data

LOOKUP Function is not case-sensitive.

LOOKUP Formula in Excel

There are 2 types of Formula for the LOOKUP function

1. The Formula of the vector form of Lookup:

LOOKUP (lookup_value, lookup_vector, [result_vector])

LOOKUP Function 1

2. The Formula of the Array form of Lookup:

LOOKUP (lookup_value, array)

LOOKUP Function 2

Arguments of LOOKUP formula in Excel

LOOKUP Formula has the following arguments:

  • Lookup_value: A value that LOOKUP searches for in an array, “Array” is a collection of values in rows and columns, it can be a text, number or a reference to a cell containing the lookup value
  • Lookup_vector: vector refers to a one-column or one-row range. It’s a single column or single row of data that is sorted in ascending order, where the LOOKUP function searches for a value in this range or when you want to specify the range or table array that contains the values that you want to match

Note: Prior to performing a lookup function, it must be sorted in ascending order.

  • Result_vector: It is a single row or single column of data that is of the same size. one-row or one-column range from where you want to return the result or It is the column or a row from where you need to fetch the required value
  • Array: is the lookup table, where you want to search for the lookup value. Mostly it will have a two-dimensional array (table) of data, values in the column or row of the array,

Note: Both the text & numeric data in the array must be sorted in ascending order.

Array form of Lookup

Note: Vlookup & Hlookup is used instead of an array form of lookup because it has a limited option.

The difference is that VLOOKUP searches in the first column, HLOOKUP searches for lookup_value in the first row and LOOKUP searches according to the dimensions of an array

Popular Course in this category
Cyber Week Sale
Excel Training (18 Courses, 9+ Projects) 18 Online Courses | 9 Hands-on Projects | 95+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (2,688 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)

How to Use the LOOKUP Function in Excel?

LOOKUP Function is very simple easy to use. Let us now see how to use LOOKUP function in Excel with the help of some examples.

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

Example #1 – Vertical Lookup formula

If your source data appears to be a vertical layout, i.e. the entries reside in columns rather than rows, it searches in a one-column range

Here, table array contains student name & registration id which is arranged vertically in columns. 

For a below-specified student name, require a registration id detail?

To obtain a registration_id, will apply lookup formula,

Under the formula toolbar, click on lookup & reference,

Formula toolbar

in that select LOOKUP function, Pop-up will appear, need to fill the function arguments to obtain the desired result

Result of Example 1

  • Lookup_value: is the value to search for. Here we need to look up “Smith” or B6 in a specified column range
  • Lookup_vector: it is the range that contains one column of text, here it is “E6:E10” which is arranged vertically
  • Result_vector: one-column range from which you want to return the result, here it is “F6:F10” which is arranged vertically.

Example #2 – Horizontal Lookup formula

If your source data appears to be a horizontal layout, i.e. the entries reside in rows rather than columns

Here, table array contains student name & registration id which is arranged horizontally in rows.

Similar to the first example, here in lookup_vector & result vector, we need to select the row range for both to obtain the desired result

Result of Example 2

  • Lookup_value: is the value to search for. Here we need to look up “Smith” or B6 in a specified row range
  • Lookup_vector: it is the range that contains one row of text, here it is “G6:K6” which is arranged horizontally
  • Result_vector: one-row range from which you want to return the result, here it is “G7:K7” which is arranged horizontally.

Things to consider prior to the application of vector form of LOOKUP

  • The Values in lookup_vector should be sorted in ascending order, i.e. from largest to smallest in case of numeric data or from A to Z in case of text data, otherwise your Excel Lookup formula may return an error or incorrect result.
  • Result_vector & Lookup_vector must be a one-row or one-column range of the same size.
  • The LOOKUP function is not case-sensitive, it does not differentiate lowercase and uppercase text. Uppercase and lowercase characters are considered equivalent
  • The Lookup formula searches for an exact match. If lookup_value doesn’t find the relevant value in lookup_vector, LOOKUP Function returns the #N/A error. 

Below mentioned examples illustrate an array form of LOOKUP Formula

Array Lookup has 2 arguments

Formula: LOOKUP (lookup_value, array)

Lookup_value – a value to search for in an array

an array is the lookup table, where you want to search for the lookup value. values in the first column or row of the array,

Note: Both the text & numeric data in the array must be sorted in ascending order.

Text & numeric data

For example: In the array, the name is located in the first column of the array (column E) and Rating in the last column of the array (column F), you can use the following formula to search the name and pull out the rating info

Example #3 – LOOKUP (lookup_value, array)

B12:C23 is lookup-range and E12 is the lookup_value. Formula result will be May as a result of the lookup value (5).

Result of Example 3

Note:

  • If the array has more columns than rows, LOOKUP searches in the first row (vertical lookup).
  • If the array has more rows than columns or the same number of columns and rows, a Lookup formula searches in the first column (horizontal lookup).
  • 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.

Recommended Articles

This has been a guide to LOOKUP. Here we discuss the LOOKUP Formula and how to use LOOKUP function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. How to Use Microsoft Excel Skills
  2. How to Improve your Microsoft Excel Skills?
  3. Top 20 Microsoft Excel Shortcuts (Advance)
  4. 10 Useful Steps to Create an Excel Dashboard 

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

11 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar
Excel Functions Tutorials
  • Lookup Reference Functions in Excel
    • 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
    • VBA VLOOKUP Function
    • 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 (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • VBA (180+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
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
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

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

EDUCBA

Download LOOKUP Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Limited Period Offer - Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More