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.
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.
We will look for the Emp ID in the master dataset and fill the first name corresponding to the Emp ID.
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.
Step 2. Click on the formula at the top of the screen, and in the lookup and reference, select VLOOKUP.
Step 3. Select the VLOOKUP option. The argument window will appear.
Step 4. Click the OK button.
There is another way you can implement the VLOOKUP function. Use the formula bar and enter the required fields there.
You will get the same result.
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.
We will get the warehouse 2 inventory details from the master table where the product code is XP200.
To use the HLOOKUP function, follow the steps mentioned below.
Step 1. Click the cell where you want the HLOOKUP formula to be calculated.
Step 2. Click formula at the top of the screen, and in the Lookup and reference, select HLOOKUP.
Step 3. Select the HLOOKUP option. The argument window will appear.
Step 4. Click OK.
To use the formula bar for HLOOKUP, go to the formula bar and enter =HLOOKUP
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.
You will get the first name corresponding to the last name.
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.
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.
We will fetch the Dept corresponding to Emp ID using vector form of Lookup
From the Lookup & Reference option, select LOOKUP. The following window will appear.
Select the vector form.
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.
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.
The argument window will appear.
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.
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 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!