What Are Excel LOOKUP Functions? The Best Introduction.

The Microsoft Excel LOOKUP function is one of the most popular excel functions. It returns a value from a range (one row or one column) or an array. We can use it as a worksheet function (WS) in Excel. This tutorial will explain the basics of Lookup in Excel and provide several examples for you to decide as to which Lookup function is best in a particular scenario.

What Is a LOOKUP Function?

Lookup functions in Excel mean referencing a cell to match values in another row or column against the cell and thereby retrieving the corresponding results from the respective rows and columns.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Uses of LOOKUP functions

Some uses of LOOKUP functions are:

  • You can find the exact or appropriate match by using the lookup function. 
  • Users can search for data both vertically (columns) and horizontally (rows).
  • It is simpler to use and does not require selecting the entire table.

Key Terms

  • Lookup - Looking for a specified value in the data table.
  • Lookup Value - A value to be searched for.
  • Return Value - A value at the same position but in another row or column depending upon whether you are doing a horizontal or vertical lookup.
  • Master Table - The table from where you will get the matching value.

VLOOKUP Function

The VLOOKUP function in Excel is a powerful function used to lookup data in a table organized vertically. It looks down the left column of a range to find a value.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup] )

Consider the master employee dataset.

master employee list

We will look for the Emp ID in the master dataset and fill the first name corresponding to the Emp ID.

vlookup table

To use the VLOOKUP function, follow the steps mentioned below.

Step 1. Click on the cell that you’d like the VLOOKUP formula to be calculated. In this case, the cell is C3.

step1

Step 2. Click on the formula at the top of the screen, and in the lookup and reference, select VLOOKUP.

step2

Step 3. Select the VLOOKUP option. The argument window will appear.

step3

Step 4. Click the OK button.

output vlookup

There is another way you can implement the VLOOKUP function. Use the formula bar and enter the required fields there.

LOOKUP manual

You will get the same result.

Free Course: Introduction to MS Excel

Master the Fundamentals of MS ExcelEnroll Now
Free Course: Introduction to MS Excel

HLOOKUP Function

The HLOOKUP function looks up for a value in the first row of a given range and gives a value in the same column from a row that you specified. 

Syntax: =HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Consider the following master table.

master inventory list

We will get the warehouse 2 inventory details from the master table where the product code is XP200.

hlookup Sheet

To use the HLOOKUP function, follow the steps mentioned below.

Step 1. Click the cell where you want the HLOOKUP formula to be calculated.

step1 hlookup.

Step 2. Click formula at the top of the screen, and in the Lookup and reference, select HLOOKUP.

step2 hlookup

Step 3. Select the HLOOKUP option. The argument window will appear.

step3-hlookup

Step 4. Click OK.

hlookup output

To use the formula bar for HLOOKUP, go to the formula bar and enter =HLOOKUP

hlookup manual

After entering the formula, hit Enter. You will get the same result.

XLOOKUP Function

The XLOOKUP function is the improved version of VLOOKUP and HLOOKUP. XLOOKUP function is used when you need to find things in a table or a range by row. The difference between VLOOKUP and XLOOKUP is that XLOOKUP can search for a return value regardless of which side the return column is on.

xlookup

You will get the first name corresponding to the last name.

output-xlookup

The if_not_found and Match_mode are optional columns and can be left blank.

LOOKUP Function

The LOOKUP function in Excel can perform the simplest types of vertical and horizontal lookups.

There are two forms of LOOKUP in Excel: Vector and Array.

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

Vector Form

The vector form looks for a value in a specified column or row.

Syntax: LOOKUP(lookup_value,lookup_vector,result_vector)

Example:

Consider the following Master table.

master-employee-list

We will fetch the Dept corresponding to Emp ID using vector form of Lookup

vlookup table

From the Lookup & Reference option, select LOOKUP. The following window will appear.

vector lookup argument

Select the vector form.

vector lookup arg1

Lookup_value is the value that LOOKUP function searches in an array. It can be a number, text, a logical value, or a name or even a reference to a value.

Lookup_vector is a range that contains only one row or one column of a text, numbers, or logical values, placed in ascending order.

Result_vector is a range that contains only one row or column, the same size as Lookup_vector.

You will get the Department corresponding to the Employee ID.

vector lookup

Array Form

The array form looks for the value in the first row or column of an array.

Syntax: LOOKUP(lookup_value,array)

Example:

From the Lookup & Reference option, select LOOKUP. The following window will appear.

Select the array form.

array form 1

The argument window will appear.

array from 2

Lookup_value is a value that LOOKUP searches for in an array and can be a number, text, a logical value, or a name or reference to a value.

An array is a range of cells that contains text, number, or logical values that you want to compare with Lookup_value.

The Pay Rate is fetched from the master table.

array form3

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

In this article, you have learned various LOOKUP functions in MS Excel. They can be used to perform many operations and tasks that are otherwise time-consuming automatically.

Boost your analytics career with powerful new Microsoft Excel skills by taking the  Business Analytics with Excel certification course, which includes Power BI training. You can even enroll for the Post Graduate Program in Business Analysis that helps you build extensive business expertise with real-world projects.

The Business Analytics course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you! 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.