• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

LOOKUP Excel Function

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

By Roshan Waingankar Leave a Comment

LOOKUP Function in Excel

Lookup can also be known as the older version of vlookup function, where in vlookup we search the reference value and match it in the entire table or data, in lookup value we do the same in a single column or row, there are two functions for lookup as both the functions as different arguments, when we use lookup function in excel we get a prompt asking which function we wish to use.

LOOKUP Function in Excel (Table of Contents)

  • LOOKUP Formula for Excel
  • Explanation of LOOKUP Function
  • Applications of LOOKUP Function
  • How to Use LOOKUP Function?

LOOKUP Excel Function

The LOOKUP Excel Function returns a value from a range of values (a row or a column) or from an array. It is a built-in function in Excel.

LOOKUP Formula for Excel

LOOKUP Excel Formula has 2 different syntaxes:

LOOKUP Formula for Excel Syntax: 1 (vector)

 LOOKUP Function Formula 

Arguments Used in This LOOKUP Formula for Excel

  • value – The value to search for.
  • lookup_vector – A row, or a column range in which a value is to be searched. The vector should be sorted in ascending order
  • result_vector – [optional] A row or a column range, which corresponds to the lookup_vector and contains the desired output.

LOOKUP Formula for Excel Syntax II: (array)

LOOKUP Function

The LOOKUP excel function searches for a value in the first row or column of the array and returns the corresponding value in the last row or column of the array.

Arguments Used in This LOOKUP Formula for Excel

  • value – The value to search for.
  • array – An array of values. The first row/column of the array is similar to the lookup_vector above (Syntax I) and the last row/column of the array is similar to the result_vector above (Syntax I).

Recommended Courses

  • Complete Financial Analyst Course
  • Online Certification Course in Excel
  • Certification Training in VBA Macros

Explanation of LOOKUP Excel Function

Depending on the size of the row and column in the given array, the function decides whether to consider the row or the column for the search. If the size of the row is greater than the size of the column, it looks for value in the first row. If the row size is less than or equal to column size, it searches value in the first column and returns the corresponding value in the last column.

The result_vector and lookup_vector should be of the same size. The LOOKUP function excel searches value in lookup_vector and returns the value having the same position in the result_vector. If this parameter is omitted, it will return the first column of data. The value, lookup_vector, and result_vector can be any datatype – a numerical value, a string, dates, currency, etc.

Output

The LOOKUP function in Excel returns any data type such as a string, numeric, date, etc. This datatype is similar to the datatype of result_vector. However, it only returns a single datatype. If there is a repetition of value in lookup_vector, it will consider the last occurrence of value in lookup_vector and return its corresponding value from the result_vector.

When the value is not present in lookup_vector

If the LOOKUP Excel function cannot find an exact match in lookup_vector, it considers the largest value in lookup_vector that is less than or equal to value. If the value is smaller than all of the values in the lookup_vector, then the LOOKUP function in excel gives an error. If lookup_vector is not sorted in ascending order, the LOOKUP function in excel will return an incorrect value. You can consider using a VLOOKUP function in such cases.

Applications of LOOKUP Function in Excel

The LOOKUP Excel Function is used to find value when one of its pairs is known. The following are some of the applications of the function:

  • Extract the price of an item using its identifier
  • Find the location of the book in the library
  • Get the last transaction by month or year
  • Check the latest price of an item
  • Find the last row in numeric/text data
  • Get the date of the last transaction

How to Use the LOOKUP Function in Excel?

LOOKUP Excel function is very simple and easy to use. Let’s understands the working of LOOKUP Excel Function with examples.

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

LOOKUP Excel Function Example #1

Suppose you have a list of items say flowers, its Identifier and it’s the latest price as shown below.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

LOOKUP Function Example 1

Now, using the ID, you can extract the price of the flower. The syntax for the same would be:

LOOKUP(ID_to_search, A5:A10,C5:C10)

The value that you want to search can also be a cell reference. Suppose the ID that you want to search is in E5, then the syntax would be:

LOOKUP(E5, A5:A10, C5:C10)

LOOKUP Function Example 1-1

The above syntax will return 50.

Similarly, you can use the flower name to search for its price. If you want to look at the price of orchid, you will give the syntax as:

LOOKUP(“orchid”, B5:B10, C5:C10)

which will return 90.

LOOKUP Function in Excel Example #2

Suppose you have a data of several transactions made since 2009 as shown below.

LOOKUP Function Example 2

Now, given any year in cell D4, you can extract the information of the last transaction done in that year using the following syntax:

=LOOKUP(D4, YEAR(A4:A18), B4:B18)

where YEAR(A4:A18) will retrieve the year from the dates in A4:A18.

LOOKUP Function Example 2-1

Since, D4 = 2012, it will return 40000.

Similarly, you can extract the last transaction done in the month of March as:

=LOOKUP(3, MONTH(A4:A18),B4:B18)

which returns 110000

LOOKUP Function in Excel Example #3

You can also retrieve the last entry of a column using the LOOKUP Excel function. Suppose you have data (list of IDs) in column B,

Example 3

you can identify the last entry in column B using the LOOKUP Excel formula:

=LOOKUP(1,1/(B:B<>””),B:B)

Here, the value is 1; lookup_vector is 1/(B:B<>””); result_vector is B:B.

B:B<>”” will form an array of true and false. True means some value is present, and false means absent. 1 is then divided by this array to form another array of 1 and 0, corresponding to true and false.

The value is 1, so it will look for 1 in the array of 1 and 0, matches with the last 1, and returns the corresponding value of the match. The corresponding value here is the actual value at that position, which is 10 in the above lookup function example.

Example 3-1

If the last value is 20 on cell B23, it will return 20 as shown below.

Example 3-2

Let us take a lookup function example of an array where you would use the syntax II.

LOOKUP Excel Function Example #4

Suppose you have an array B3:I24 having the student’s roll number (ID) in the 1st column, followed by their names, marks in five different subject and average marks secured in the last column as shown below.

Example 4

You can retrieve the average marks of any student using his/her ID. If the ID to look for is in cell K4, then the syntax would be given as:

LOOKUP(K4, B4:I24)

Example 4-1

It will return the corresponding average marks of the student.

Things to Remember

  • The lookup_vector must be sorted in ascending order.
  • The result_vector and lookup_vector should be of the same size.
  • When the value is not found in lookup_vector, the function matches the largest value in lookup_vector that is less than or equal to value.
  • If the lookup_value is greater than all values in  lookup_vector, the function matches the last value.
  • When the value is less than the smallest value in lookup_vector, the function returns an error (#N/A).
  • It is not case-sensitive.

LOOKUP Excel Function Video

Recommended Articles

This has been a guide to LOOKUP Excel Function. Here we discuss the LOOKUP Formula and how to use LOOKUP function in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel

  • VLOOKUP with SUM Function | Explanation | Examples
  • How to Use VLookup with IF Statement in Excel?
  • How to Sort in Excel?
  • LARGE Formula in Excel
  • SMALL Formula in Excel
  • How to use MONTH on Excel
  • EXACT Excel Function
  • AVERAGE Excel Function
  • Match Excel Function
  • INDIRECT Excel Function
  • HLOOKUP Excel Function
  • GetPivotData Excel Function
2 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Lookup & Reference Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

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

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

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.

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

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

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

WallStreetMojo

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

WallStreetMojo

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

WallStreetMojo

Download LOOKUP Function Excel Template

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

Limited Period Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More