MS Excel: How to use the LOOKUP Function

0 0
Read Time:6 Minute, 36 Second

This Excel tutorial explains how to use the Excel LOOKUP function with syntax and examples.

Description

The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array.

The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the LOOKUP function can be entered as part of a formula in a cell of a worksheet.

There are 2 different syntaxes for the LOOKUP function:

LOOKUP Function (Syntax #1)

In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position.

The syntax for the LOOKUP function in Microsoft Excel is:

LOOKUP( value, lookup_range, [result_range] )

Parameters or Arguments

value
The value to search for in the lookup_range.
lookup_range
A single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range.
result_range
Optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, it will return the first column of data.

Returns

The LOOKUP function returns any datatype such as a string, numeric, date, etc.
If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A.
If the values in the LOOKUP_range are not sorted in ascending order, the LOOKUP function will return the incorrect value.

Applies To

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

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let’s look at some Excel LOOKUP function examples and explore how to use the LOOKUP function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following LOOKUP examples would return:

=LOOKUP(10251, A1:A6, B1:B6)
Result: "Pears"

=LOOKUP(10251, A1:A6)
Result: 10251

=LOOKUP(10246, A1:A6, B1:B6)
Result: #N/A

=LOOKUP(10248, A1:A6, B1:B6)
Result: "Apples"

LOOKUP Function (Syntax #2)

In Syntax #2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.

The syntax for the LOOKUP function in Microsoft Excel is:

LOOKUP( value, array )

Parameters or Arguments

value
The value to search for in the array. The values must be in ascending order.
array
An array of values that contains both the values to search for and return.

Returns

The LOOKUP function returns any datatype such as a string, numeric, date, etc.
If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A.
If the values in the array are not sorted in ascending order, the LOOKUP function will return the incorrect value.

Applies To

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

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let’s look at some Excel LOOKUP function examples and explore how to use the LOOKUP function as a worksheet function in Microsoft Excel:

=LOOKUP("T", {"s","t","u","v";10,11,12,13})
Result: 11

=LOOKUP("Tech on the Net", {"s","t","u","v";10,11,12,13})
Result: 11

=LOOKUP("t", {"s","t","u","v";"a","b","c","d"})
Result: "b"

=LOOKUP("r", {"s","t","u","v";"a","b","c","d"})
Result: #N/A

=LOOKUP(2, {1,2,3,4;511,512,513,514})
Result: 512

Frequently Asked Questions

Question: In Microsoft Excel, I have a table of data in cells A2:D5. I’ve tried to create a simple LOOKUP to find CB2 in the data, but it always returns 0. What am I doing wrong?

Answer: Using the LOOKUP function can sometimes be a bit tricky so let’s look at an example. Below we have a spreadsheet with the data that you described.

In cell F1, we’ve placed the following formula:

=LOOKUP("CB2",A2:A5,D2:D5)

And yes, even though CB2 exists in the data, the LOOKUP function returns 0.

Now, let’s explain what is happening. At first, it looks like the function isn’t finding CB2 in the list, but in fact, it is finding something else. Let’s fill in the empty cells in D3:D5 to explain better.

If we place the values TEST1, TEST2, TEST3 in cells D3, D4, 5, respectively, we can see that the LOOKUP function is in fact returning the value TEST2. So we ask ourselves, when we are looking up CB2 in the data and CB2 exists in the data, why is it returning the value for CB19? Good question. The LOOKUP function assumes that the data in column A is sorted in ascending order.

If you look closer at column A, it is not in fact sorted in ascending order. If we quickly sorted column A, it would look like this:

Now the LOOKUP function correctly returns 3A when it is looking up CB2 in the data.

To avoid these sorting problems with your data, we recommend using VLOOKUP function in this case. Let’s show you how we would do this. If we changed our formula below (but left our data in column A in the original sort order):

The following VLOOKUP formula would return the correct value of 3A.

=VLOOKUP("CB2",$A$2:$D$5,4,FALSE)

The VLOOKUP function does not require us to have the data sorted in ascending order since we used FALSE as the last parameter – which means that it is looking for an exact match.


Question: I have the following LOOKUP formula:

=LOOKUP(C2,{"A","B","C","D","E","F","G","H","I","K","X","Z"}, {"1","2","3","4","5","6","7","8","9","10","12","1"})

I also need to add zero to the lookup vector and result vector. How do I do this?

Answer: Using numbers in Excel can be tricky, as you can enter them either as numeric or text values. Because of this, there are 2 possible solutions.

Numeric Solution

If you have entered your zero as a numeric value, then the following formula will work:

=LOOKUP(C2,{0,"A","B","C","D","E","F","G","H","I","K","X","Z"}, {0,"1","2","3","4","5","6","7","8","9","10","12","1"})

Text Solution

If you have entered your zero as a text value, then the following formula will work:

=LOOKUP(C2,{"0","A","B","C","D","E","F","G","H","I","K","X","Z"}, {"0","1","2","3","4","5","6","7","8","9","10","12","1"})

Question: For the following function in Microsoft Excel:

=LOOKUP(M14,Sheet2!A2:A2240,Sheet2!B2:B2240)

How do I get it to return a blank cell if the LOOKUP value (M14) is blank?

Answer: To check for a blank value in cell M14, you can use the IF function and ISBLANK function as follows:

=IF(ISBLANK(M14),"",LOOKUP(M14,Sheet2!A2:A2240,Sheet2!B2:B2240))

Now if the value in cell M14 is blank, the formula will return a blank. Otherwise it will perform the LOOKUP function as before.

Loading

Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %
Print Friendly, PDF & Email

About Author

  • Related Posts

    Average Rating

    5 Star
    0%
    4 Star
    0%
    3 Star
    0%
    2 Star
    0%
    1 Star
    0%

    Incase you missed

    Best Places to get Accounting Internship Opportunities

    Best Places to get Accounting Internship Opportunities

    Top Kenyan Based Companies to apply for internship

    Top Kenyan Based Companies to apply for internship

    Credit Scores Quickk FAQs

    Credit Scores Quickk FAQs

    Credit Score Ranges: What Do They Mean?

    Credit Score Ranges: What Do They Mean?

    RANDOMIZING PHONE NUMBERS IN EXCEL

    RANDOMIZING PHONE NUMBERS IN EXCEL

    [Updated 2024] – Passport Application FOR CHILDREN ONLY(PERSONS UNDER 18 YEARS)

    [Updated 2024] – Passport Application FOR CHILDREN ONLY(PERSONS UNDER 18 YEARS)

    [Updated 2024] -Passport Application FOR ADULTS ONLY-PERSONS OVER 18 YEARS

    [Updated 2024] -Passport Application FOR ADULTS ONLY-PERSONS OVER 18 YEARS

    Understanding Tires Load Index vs Load Range on Car Tires

    Understanding Tires Load Index vs Load Range on Car Tires

    Computer Bus: Video

    Computer Bus: Video

    Computer Bus

    Computer Bus

    Types of Computer Network Topology Explained With Diagrams

    Types of Computer Network Topology Explained With Diagrams

    The Functions of PCI Slots

    The Functions of PCI Slots

    Complementary Metal Oxide Semiconductor – CMOS

    Complementary Metal Oxide Semiconductor – CMOS

    The Computer Motherboard and Its Components Explained

    The Computer Motherboard and Its Components Explained

    AI in Education is Best Experiences

    AI in Education is Best Experiences

    Harnessing the Power of Wind Energy

    Harnessing the Power of Wind Energy

    The Golden Gate’s Timeless Majesty

    The Golden Gate’s Timeless Majesty

    Rise of Competitive Video Gaming

    Rise of Competitive Video Gaming

    Ultimate Sports Ground Experience

    Ultimate Sports Ground Experience

    Global Ocean Cleanup Initiatives

    Global Ocean Cleanup Initiatives

    The Enduring Appeal of Gaming Classics

    The Enduring Appeal of Gaming Classics

    The Art of Mindful Eating

    The Art of Mindful Eating

    Classic Video Games Making a Comeback

    Classic Video Games Making a Comeback

    Sustainable Travel for Eco-Friendly Tourism

    Sustainable Travel for Eco-Friendly Tourism

    Mind-Body Connection for Meditation

    Mind-Body Connection for Meditation

    Future of Work Like Remote Collaboration Tools

    Future of Work Like Remote Collaboration Tools

    Innovations in 3D Printing

    Innovations in 3D Printing

    Digital Detox Unplug and Reconnect

    Digital Detox Unplug and Reconnect

    Crafting Engaging Audio Experiences

    Crafting Engaging Audio Experiences

    Advancements in Digital Camera

    Advancements in Digital Camera

    Advanced progress in the workplace

    Advanced progress in the workplace

    Navigating the Landscape of Cryptocurrency Trends

    Navigating the Landscape of Cryptocurrency Trends

    Exploring Deep Sea Exploration

    Exploring Deep Sea Exploration

    Plant Based Alternatives Reshaping Food

    Plant Based Alternatives Reshaping Food

    Space tourism soars into the forefront of exploration

    Space tourism soars into the forefront of exploration

    Fashion Forward: Sustainable Fashion Brands

    Fashion Forward: Sustainable Fashion Brands

    AI in Healthcare: Game-Changer or Ethical Dilemma?

    AI in Healthcare: Game-Changer or Ethical Dilemma?

    VR Trends Reshaping Entertainment

    VR Trends Reshaping Entertainment

    Tech Giants Unite for Sustainability

    Tech Giants Unite for Sustainability

    Sleep Study Unveils New Insights

    Sleep Study Unveils New Insights

    How to Download Your p9 Form from Ghris Portal

    How to Download Your p9 Form from Ghris Portal

    How to Download Previous Payslips from Ghris

    How to Download Previous Payslips from Ghris

    Steps on Downloading and Print GHRIS Payslip Online

    Steps on Downloading and Print GHRIS Payslip Online

    Link a Business on Ecitizen Portal

    Link a Business on Ecitizen Portal

    How to Update the Beneficial Ownership Information

    How to Update the Beneficial Ownership Information

    How to Reset Epson L382 Printer

    How to Reset Epson L382 Printer

    List Of Accredited TVET Institutions In Kenya – Ms Excel Search Form

    List Of Accredited TVET Institutions In Kenya – Ms Excel Search Form

    List Of Accredited TVET Institutions In Kenya

    List Of Accredited TVET Institutions In Kenya

    UNIT TITLE : INTEGRATED SCIENCE

    UNIT TITLE	: INTEGRATED SCIENCE

    How to install zip extension in php 8.0/8.1 in Cyberpanel

    How to install zip extension in php 8.0/8.1 in Cyberpanel

    Top 10 tourist centers in kenya with brief notes

    Top 10 tourist centers in kenya with brief notes

    What is Rental Income?:KRA Itax Services

    What is Rental Income?:KRA Itax Services

    GETTING STARTED ON KRA PIN WHY & HOW TO ACQUIRE ONE

    GETTING STARTED ON KRA PIN WHY & HOW TO ACQUIRE ONE

    Kenyan Passport : Submission and Biometrics capture

    Kenyan Passport : Submission and Biometrics capture

    Kenyan Passport : Requirements for Lost passports

    Kenyan Passport : Requirements for Lost passports

    Kenyan Passport : Requirements for Mutilated Passports

    Kenyan Passport : Requirements for Mutilated Passports

    Kenyan Passport : Requirements for Replacement of passports

    Kenyan Passport : Requirements for Replacement of passports

    Kenyan Passport: Requirements for New (First time) applications

    Kenyan Passport: Requirements for New (First time) applications

    Dolibarr FEATURES Main application/modules (all optional)

    Dolibarr FEATURES Main application/modules (all optional)

    REASONS FOR CHOOSING DOLIBARR AS YOUR Open Source ERP & CRM for business

    REASONS FOR CHOOSING DOLIBARR AS YOUR Open Source ERP & CRM for business