EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel LOOKUP in Excel
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
    • Compare Two Columns in Excel using VLOOKUP
    • 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 (220+)
  • 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

LOOKUP in Excel

By Pradeep SPradeep S

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 specific data in one row or one column, it will use the lookup function; its main purpose is to Lookup 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 formulas for the LOOKUP function.

1. Formula of the vector form of Lookup

LOOKUP (lookup_value, lookup_vector, [result_vector])

LOOKUP Function 1

2. 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.

The 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 an array’s dimensions.

How to Use the LOOKUP Function in Excel?

LOOKUP Function is straightforward, easy to use. Let us now see how to use the 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, the table array contains the 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, a Pop-up will 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, the table array contains the 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, the 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. The 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 the LOOKUP function in Excel and practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. IF VLOOKUP Formula in Excel
  2. VLOOKUP Examples in Excel
  3. Lookup Table in Excel
  4. VBA LOOKUP
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
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

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

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

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

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download LOOKUP Function Excel Template

EDUCBA Login

Forgot Password?

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

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