Microsoft Office

LOOKUP Types Function and Formula

LOOKUP Types Function and Formula

What is Lookup in MS Excel?

LOOKUP Types Function and Formula: In Excel, the Lookup function is an important tool for finding specific values in the large range of data. This function is particularly useful when you need to take out a particular information based on specific criteria or match values from one dataset to another.

While it is less commonly used today compared to functions like VLOOKUP or HLOOKUP, understanding LOOKUP provides basic insight of how Excel handles search and retrieval tasks.

Uses of LOOKUP function in MS Excel

The Lookup is a very useful function for searching a particular data from a large number of datasets. Some popular uses of lookup function are given below:

  • Finding data: The main use of the Lookup function is to find a value in one range and return a corresponding value from another range.
  • Managing Similar Data: The Lookup function is useful when you need to manage similar matches. It gives the largest value that is less than or equal to the lookup value when an exact match is not found.
  • Handling Actual data: Lookup is useful for finding data from actual records where the data might be in a single-column or single-row format.
  • Sorted data: In case your data is sorted in ascending order, lookup can efficiently find values without the help of more complex formulas.
  • Creating data tables: Lookup helps in creating data tables automatically with the data from other sources which have similar information.
  • Conditional formatting: You can apply formatting rules based on lookup results, highlighting items that meet certain criteria.
  • Querying data: By using Lookup function we can retrieve information from databases based on specific criteria.
  • Scenario analysis: Simulate different outcomes by changing input values and using lookup functions to update calculations.
  • Data validation: Lookup function ensures data accuracy by checking for inconsistencies or errors using lookup functions.

Types of Lookup function

In Excel, there are commonly three types of Lookup functions available which we will discuss below:

  • LOOKUP Function
  • VLOOKUP Function
  • HLOOKUP Function

LOOKUP Function

LOOKUP is a useful function in Excel which is used to search for a value in one column or row and return a corresponding value from another column or row.

The syntax of LOOKUP function is =LOOKUP(lookup_value, lookup_vector, [result_vector]) where lookup_value denotes the value that you want to search, lookup_vector denotes a range of cells or data in which you want to search for that value and result_vector denotes a range of cells from which you want to return the result. 

Steps for LOOKUP Function in Excel

  1. First arrange your data into ascending order.
  2. Now suppose you want to search Sneha’s post. Then type =lookup( then select the cell for which you want to search. Here we have selected Sneha whose cell address is M3.
  3. After that put a comma and select the range of cell or data in which you want to search from. Here we have selected the following data table whose cell address is B3:E7.
  4. Then put a comma and select the post column as we want to search Sneha’s post. The cell address is D3:D7 as shown below.
  5. Now close the bracket and press Enter. 
  6. The Sneha’s post has been searched.
LOOKUP Syntax
LOOKUP Value

VLOOKUP Function

VLOOKUP is a powerful function in Excel that searches for a value vertically in a table or range and returns the corresponding value from a specified column. The “V” in VLOOKUP stands for “Vertical,” which means it searches for a value in the first column and pulls out a value from another column in that same row.

The syntax of VLOOKUP function is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) where lookup_value denotes the value that you want to search, table_array denotes the range of cells or data tab in which you want to search for that value, col_index_num denotes the column number in the data table from which you want to search the value and range_lookup denotes a logical value that specifies whether to find an exact match or an approximate match. 

If your data is arranged in ascending order you can use TRUE for approximate value. The TRUE value can be indicated by 1. If your data is not arranged in ascending order you can use FALSE for exact value. The FALSE value can be indicated by 0.

Steps for VLOOKUP Function

  1. Suppose you want to search Kartik’s salary. Type =vlookup( then select the cell for which you want to search. Here we have selected Kartik whose cell address is M3.
  2. After that put a comma and select the range of cell or data in which you want to search from. Here we have selected the following data table whose cell address is B2:E7.
  3. Then put a comma and write the column number from which you want to search for the value. Here we want to search for salary so the salary column number is 2 which we have entered as shown.
  4. Now select the logical value, here we have written 0 for FALSE (exact match) because our table is not sorted.
  5. Now close the bracket and press Enter
  6. The Kartik’s Salary has been searched.
VLOOKUP Syntax
VLOOKUP Value

HLOOKUP Function

HLOOKUP is also a powerful function in Excel that searches for a value horizontally in a table or range and returns the corresponding value from a specified row. The “H” in HLOOKUP stands for “Horizontal,” which means that it searches for a value in the first row of a table and returns a value in the same column from another row.

The syntax of HLOOKUP function is =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) where lookup_value denotes the value that you want to search, table_array denotes the range of cells or data tab in which you want to search for that value, row_index_num denotes the row number in the data table from which you want to search the value and range_lookup denotes a logical value that specifies whether to find an exact match or an approximate match. 

If your data is arranged in ascending order you can use TRUE for approximate value. The TRUE value can be indicated by 1. If your data is not arranged in ascending order you can use FALSE for exact value. The FALSE value can be indicated by 0.

Steps for HLOOKUP Function

  1. Suppose you want to search Kartik’s post. Type =hlookup( then select the cell for which you want to search. Here we have selected Kartik whose cell address is M3.
  2. After that put a comma and select the range of cell or data in which you want to search from. Here we have selected the following data table whose cell address is B2:E7.
  3. Then put a comma and write the row number from which you want to search for the value. Here we want to search for post so the post row number is 4 which we have entered as shown.
  4. Now select the logical value, here we have written 0 for FALSE (exact match) because our table is not sorted.
  5. Now close the bracket and press Enter
  6. The Kartik’s Post has been searched.
HLOOKUP Syntax
HLOOKUP Value

Advantages of Lookup function

Using Lookup in various ways can help you to work effortlessly. There are multiple advantages of Lookup function which are as follows:

  • Quickly Data Finding: The Lookup function helps in searching a data value in a range and give a similar value from another range. This is useful when working with large datasets as manual searching would be time-consuming.
  • Multiple Lookup Options: Lookup functions like Vlookup and Hlookup,  you can choose the right function according to your data.
  • Less Time-Consuming: Once you set up, Lookup functions automates the searching of data, which saves time compared to manual lookup process.
  • Simplifies Complex Calculations: Lookup functions can be combined with other Excel functions which performs more complex calculations in an easier way.
  • Managing Large Datasets: Lookup functions can manage large datasets efficiently, enabling quick searches and data finding from large datasets without slowing down performance.
  • Widely Supported: Lookup, Vlookup, and Hlookup are supported in all versions of Excel, making them reliable tools for users working with different versions of the software.
  • Simple Syntax: The basic syntax of these functions is easy, making them accessible to even less experienced users.
  • Dynamic Criteria: You can use formulas or cell references as criteria, which makes your lookup more flexible.

Conclusion

In this blog, we have discussed that how Lookup function in MS Excel can be used and how it makes your work easier. I hope you have find this blog useful. If you want to get more such information related to MS Excel features, formulas and functions, then please visit our blog.

About the author

Pooja Rastogi

Leave a Comment