Excel with Look up Function:

VLOOKUP:-Vlookup is an excel function to lookup and retrieve data from a specific column. Vlookup support approximate and exact match. The v stands for vertical lookup.

Syntax:-  = VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP])

Parameters:-

Lookup_Value:- the  value you want to look for.

Table_Array:- table from which retrieve the values.

Col_Index_Num:- the column of the table from which you want to retrieve the value.

Range_lookup:- It is optional it will show exact match or approximate match.

Note:-  if you don’t  use range look up then by default it will take exact match.

EXAMPLE:-

In above example we need to find what is the Dept of the employee_id 146879  so we will use the syntax and it gives result . Lookup value is (C8) Is the reference, second argument is array reference (A2:D6), Column, the column number whose value the function should return value, false .

HLOOKUP:-

Syntax –   =HLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,ROW_INDEX_NUM,[RANGE_LOOKUP])

Parameters:-

Lookup_Value:- the  value you want to look for.

Table_Array:- table from which retrieve the value.

Row_Index_Num:- the row of the table from which you want to retrieve the value.

Range_lookup:- It is optional it will show exact match or approximate match.

In above example we need to find what is the value for the month feb for the column  so we will use the syntax and it gives result as 100. Lookup value is (D7) is the reference , second argument is array reference (A1:F4),Row  the row number whose value the function should return value, false  for matching exact match.

LOOKUP FUNCTION:-

There are two different syntaxes used in lookup function:-

Lookup vector:-It is helpful when we have two different arrays one for look up vector and one for lookup range or array.

Syntax 1:-=LOOKUP(LOOKUP_VALUE,LOOKUP_VECTOR,RESULT_Vector)

Parameters:-

Lookup_Value :– The  value you want to look for in the data set.

Lookup_Vector:- The range of cells where the value is present that you want to look for ,it can be vertical or horizontal.

NOTE:- The look up vector should be sorted in ascending order.

Result_Vector:-. A range of cells that contains value you want to return.

Looking into  the data its is a call log of an company, here we find the calls attended by david in the feb,  so we will use syntax (A11) is the lookup_value, (A2:A8) is the lookup_array, and  (C2:C8) is the result _vector.

LOOKUP ARRAY:-  Function searches for the value  in the first row or column of an array and returns

The corresponding value in the last row or column of the array.

SYNTAX 2:- =LOOKUP(LOOKUP_VALUE,ARRAY)

Parameters:-

Lookup_Value :- the  value you want to look for in the dataset.

Array:- the array of values that contains value you want to look for.

Now by using the Lookup Function we need to find what is the total no of call logs of the Name.=lookup(A11) is used  for the employee name david for which you are searching the call logs he has received.

MATCH

This function looks for an item in a list and shows its position. It can be used with text and numbers,It can look for an exact match or an approximate match.

Note:- Data should  be arranged in ascending or descending order.

Syntax:-Match(Lookup_Value,Lookup_Array,[Match_type ])

Lookup_value:- the value you want to look for in the dataset.

Lookup_Array:- the array of values that contains value you want to look for.

Match_type:- It is type of match it will be either 0,1 or -1.

This is name of employees, if  i want to search an position of an employee name Alan then it will give the value 2. (D7) is the look_up value,( A2:A5) is the lookup_array and (0) is the exact match.

Index:-

This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns. It can be used with a single block of data, or non-continuous blocks.

Syntax:- =Index(array,row_num,column_num)

Array:-table from which retrieve the value.

Row_num:– No.Of Range in horizontal

Column_Num:- No of range in vertical

Here, in this example the price of booking tickets we have to find the cost per person here (B3:E5) is the array, (E7) no of weeks,(E8) no.of people in the party. So the cost per person will be 250.

INDEX AND MATCH:-

This is an extended version of the previous example .It allows the names of products and the quarters to be entered. The =MATCH () function is used to find the row and column positions of the names entered. These positions are then used by the =INDEX () function to look for the data.

The pupils scores are compared against the breakpoints.If an exact match is not found, the next lowest breakpoint is used.The =INDEX () function then looks down the Grade list to find the grade. Here, (B2:B5) is an array,(E2) is the lookup value ,(A2:A5) is the exam score,1 is the approximate match.

Difference between Index Match And V-Lookup Function:-

1. Difference between INDEX MATCH and VLOOKUP is that VLOOKUP requires a static column reference while INDEX MATCH uses a dynamic column reference.
2. When using INDEX MATCH, you can append these new lookup keys to the right side of your table and perform a right-to-left lookup to pull the values but in VLOOKUP, because you can only perform a left-to-right lookup, any new lookup key you add must be on the left side of your original table array.
3. INDEX MATCH doesn’t required sorting of data where as VLOOKUP requires sorting of data.

References:-

1. http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/
2. https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
3. https://www.techonthenet.com/excel/formulas/lookup.php

About Shyama:

Shyama is an MBA. Currently she is working as an Analyst Intern with NikhilGuru Consulting Analytics Service LLP, Bangalore. She has prior worked for around 3 Years with Apollo Hospitals.