In the previous tutorial we have seen that VLOOKUP can be
used to extract the data.
Let’s explore the formula of VLOOKUP in detail VLOOKUP(lookup_value,
table_array, col_index_num, [range_lookup])
Lookup_value is the value to be searched in the table which
has all the required data to be searched.
For example, it can be the Name of the person. Based on the
name of the person, other details about the person can be extracted from the
table which contains the desired data.
The table_array refers to the table from which we want the
desired data.
The values in the first column of table_array are the values
searched by lookup_value.
The first column in both the tables i.e., the table where
you want to extract the data and the table where you want the desired data
should be the same.
For example, in both the tables Name should be the first
column if you want to extract the data according to the Name.
Col_index_num refers to the number of the column (belonging
to the table_array) from which you want the data.
For example, I want the data related to the Age of the
person from the name of the person. Assume Age is the second column. So the
col_index_num should be 2.
Range_lookup - A logical value that specifies whether you
want VLOOKUP to find an exact match or an approximate match. Specify FALSE for
exact match and TRUE for approximate match.
Now the problem with the previous method is that there can
be a case where the lookup_value might not be unique. For example, consider a
table consisting of Name, Occupation and Age of the people and another table
consisting the Name, Occupation and Salary information. I want to extract the
data related to the Salary from the second table. I can use Name as the
lookup_value. But two people can have the same Name. Hence, Name is no more the
unique identifier.
In such cases, key can be used.
Key is formed by simply merging the information such that it
should be unique.
Let’s take another example:
Goal: VLOOKUP using Key.
Solution: Consider a table (Table 1) as below in the first sheet of
your workbook.
Consider another table (Table 2) as below
This table has additional information related to the Subject each student has opted for.
Our aim is to get the exact information related to the Subjects (as per the student has opted for) in Table 1 from Table 2.
Create a key as shown below in Table 1.
This key will function as a unique identifier. Do drag and fill the remaining rows in the manner shown below and do perform a copy and paste special to keep only the values.
In a similar manner create a key in the second table.
To perform this first insert another column as shown below. This column will have unique key as formed previously.
The Table 2 after forming the key is as shown below. Again drag and perform copy and paste special to keep only the values.
Now we are all set to perform VLOOKUP. Name a column Subject in Table 1 besides the Key column. This column will have the data related to the Subject opted by a particular student.
Perform the below steps (VLOOKUP) to get the required data.
Step 1:
Step 2: Table_Array is Table 2 with Key and Subject column as below
Step 3:
Column number should be specified from which you want the data. Subject in this case is column number 2.
Check the formula bar for the column number.
Step 4: Specify False for an exact match.
The entire formula is as follows:
=VLOOKUP(D2,Sheet2!D1:E6,2,FALSE)
Drag and perform paste special to get all the values. The Table 1 after performing VLOOKUP is as shown below.
One can notice that we had data related to Andy studying in the 2nd standard as well as the 3rd standard. Due to the unique key the correct data was extracted as expected.
After removing the Key column (as it is not required any longer) Table 1 is as follows:
That's the way/key to use VLOOKUP effectively!! :)
