Excel Lookup Functions

Excel Lookup functions

Microsoft Excel:– Lookup functions in Microsoft Excel are in-build-functions which are used for searching the data in Microsoft Excel. It searches the data from a range of cells (both from rows or columns) or also from an array in Microsoft excel. Lookup functions in Microsoft Excel make your searches easier in excel, as you are searching for a particular result in your worksheet, it will be easier to find that result with the help of lookup functions in Microsoft Excel. These functions in excel save your time and make your searches more accurate and reliable. In this article, we will discuss the lookup functions and references in Microsoft excel.

There are mainly four types of lookup functions in Microsoft excel namely:-

  • Vlookup
  • Hlookup
  • Match
  • Index

Vlookup Function in Microsoft Excel

Vlookup stands for vertical lookup. The vlookup function in excel searches the result from the leftmost column of the table or array in Microsoft Excel. It searches from left to right in the columns of the table and provides result in the same row from the column that you specify in Excel.

Syntax of Vlookup function in excel:-

=vlookup(lookup_value, table_array, col_index_num, [range_lookup])

The Vlookup function Contains:-

  • Lookup_value:- Lookup_value means the value on which basis you are searching for your result in the table_array i.e. the value which is used to find the result in the table_array. The lookup value must be always the leftmost first column of the table_array.
  • Table_array:- Table_array is the range of cells in which you are searching for the result. The range of table_array should be always from that column of the table which contains the lookup_value.
  • Col_index_num:- Col_index_num means column index number. You should provide the column number of the result you are searching for from the range of table_array.
  • Range_lookup:- Range_lookup contains two values. First one is the Approximate match and second is the exact match. Approximate match means you will get the irrelevant (incorrect) result from the table_array in excel. In Vlookup, the approximate match can be given as True or as 1. On the other hand, Exact match means you will get the accurate (correct) result which you want from the table_array. In Vlookup, the Exact match can be given as False or as 0.

Example of Vlookup Function

Here, we take an example of employees’ salary details and we have to search for the salary details on the basis of the Employees code.

  • Create a vertical table with the following fields- Employee code, Employee name, Basic salary, HRA, DA, PF, Tax, Gross Salary, Net salary.
  • Here we have to Search Emp_name, Basic salary, HRA, DA, PF, Tax, Gross Salary, and net salary on the basis of Emp_code. Therefore, Emp_code will be the lookup value.
  • On a blank cell (or where you want to show your search result) type the lookup value i.e. Emp_code.
  • Now, on other cells, type the fields which you want to search on the basis of Emp_code i.e. Emp_name, Basic salary, HRA, DA, PF, Tax, Gross Salary, and Net salary.
  • To search Emp_name, lookup value will be the cell next to Emp_code, table array will be the range of the table, column index number will be 2 (as the column number of Emp_name is 2 in the selected range of table array) and lookup range will be false or 0 for an exact match.
lookup function in excel megvilla
  • Repeat the same formula to search the Basic salary, HRA, DA, PF, Tax, Gross Salary, and Net salary. Only the column index number of the following fields will be different.
lookup function in excel megvilla

Hlookup Function in Excel

Hlookup stands for horizontal lookup. The Hlookup function in excel searches the result from the topmost row of the table or array. It searches from top to bottom in the rows of the table or array and provides result in the same column from the row that you specify in excel.

Syntax of Hlookup function:-

=hlookup(lookup_value, table_array, row_index_num, [range_lookup])

The Hlookup function Contains:-

  • Lookup_value:- Lookup_value means the value on which basis you are searching for your result in the rows of the table_array i.e. the value which is used to find the result in the table_array. The lookup value must be always the topmost first row of the table_array.
  • Table_array:- Table_array is the range of cells in which you are searching for the result. The range of table_array should be always from that row of the table which contains the lookup_value.
  • Row_index_num:- Row_index_num means Row index number. You should provide the column number of the result you are searching for from the range of table_array.
  • Range_lookup:- Range_lookup contains two values. First one is the Approximate match and second is the exact match. Approximate match means you will get the irrelevant (incorrect) result from the table_array. In this, the approximate match can be given as True or as 1. On the other hand, Exact match means you will get the accurate (correct) result which you want from the table_array. In Hlookup, the Exact match can be given as False or as 0.

Example of Hlookup Function

There is an example of Employee’s details and there we have to search for the details of the employees on the basis of employees’ id in excel.

  • To apply hlookup function, firstly we have to create a horizontal table. So, the row headings will be Emp_id, Emp_name, Designation, Department, and Salary.
  • Then on the blank cells, type the lookup value, i.e. Emp_id and the fields that you want to search from the table on the basis of Emp_id.
  • Now, to search the Emp_name on the basis of Emp_id, apply hlookup function on the Emp_name. Here, lookup value will be the cell next to Emp_id, where you will enter the Id of the employee, table array will the range of the table, row index number will be 2, as the row number of Emp_name in the table array is 2 and range lookup will be false or 0 for accurate results.
lookup function in excel megvilla
  • Apply the same function to search Designation, Department, and Salary of the employees.
lookup function megvilla

Index Function

The Index function in Microsoft excel searches for a result in a particular row and column of the table array. Unlike, Vlookup and Hlookup, Index function can search from any row and column from the table array. It is not mandatory to find the result from the leftmost column or topmost row of the table array in excel.

Syntax of Index function:-

=Index (array, row_num, [column_num])

The Index function Contains:-

  • Array:- Array in Index function is the range of cells or the range of table in which you want to search the result.
  • Row_num:- Row number means the number of that row in the table array whose result you want to search from the row.
  • Column_num:- Column number means the number of that column in the range of the array whose result you want to search in the column.

Example of Index Function

In this example, we take the details of different students and here we have to search the total marks of the student.

  • Firstly, create the table of the student’s details such as Roll no., Name, Marks in different Subjects, Total and Average.
  • Now on any blank cell, apply the index function. Here, if we want to search the total marks of student Gagan then, array will be the range of the table of student’s details, row number will be 6 (as the record of student Gagan is in the 6th row of the table array) and column number will be 8 (as the column number of total in the given range of array is 8).
Index function

Match Function in Microsoft Excel

Match function in Microsoft Excel is used to find the relative position of a specified value in the row or column. In short, match function is used to find the column number or row number of particular value in the given range of cells.

Syntax of Match function:-

=match(lookup_value, lookup_array, [match_type])

The Match function Contains:-

  • Lookup_value:- Lookup value means the value whose column or row number you want to find in the given range of cells.
  • Lookup_array:- Lookup array is the range of columns or rows from where you want to search the result. If you are searching for a column number select the range of the columns and if you are searching for a row number then select the range of the rows.
  • Match_type:- In match function there are three types of match. First one is 1 or less than, this type of match finds the largest value that is less than or equal to the lookup value. Second one is 0 or exact match, this type of match finds the exact Value that you are searching for in the table array and the third type of match is -1 or  greater than, this type of match, finds the smallest value that is greater than or equal to the lookup value in the given range of cells.

Example of Match Function

The following example will show you how to apply the match function in Microsoft Excel.

  • Firstly, we are finding the row number of product laptop. Type laptop on a blank cell and on the other cell apply the match function. Here, lookup value will be a laptop, lookup array will be the row range of product in the table and match type will be 0 or exact match.
match Function in  excel
  • Secondly, we are looking for the column number of purchase in the table. So, type purchase on a blank cell and on the other cell apply the match function.  Here, lookup value will be Purchase, lookup array will be the range of columns in the table and match type will be 0 or exact.
match Function megvilla

Rahul Dass

Leave a Reply

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

Back to top
shares
error

Enjoy this blog? Please spread the word :)