VLOOKUP SIMPLIFIED!!!!
Basics: Vlookup (vertical lookup) and Hlookup (Horizontal lookup) is a function in excel which allows you to search / transfer data column wise and row wise respectively between two values / tables.
Since Vlookup is the most commonly used function in excel here in this post we will concentrate only on Vlookup.
Lookup_value: The value we want to search
Table_array: table in which we want to search (rem the value we are looking for must be the first column of the table)
Col_index_num: its column no. from left to right starting from 1st column of the table_array
Range_lookup: defines if we want an exact match or an approximate match (we will discuss both)
Example: We have 10 students with their marks in value as well as percentage
Now in another table we want only marks for 6 pre-selected students. In this case we can easily use vlookup for extracting the data from the database / table_array
In the first function we had find B in the table_array marked in Red. Since we need marks/ 3rd Column will be the index from left to right staring from 1st column of the table array. There after putting Zero / False will help you get the exact value or else you will get #N/A just in the 6th Case were W was not found
As you copy the formula in below cells for getting the data the table array shift too. This may give you wrong results In this case we have A in table array still the formula could not search the same. To avoid this most common mistake please make sure that table_array in formula is grouped with $ sign (select only table_array in formula and press F4) as per below table.
There after the final result that we get looks like this. Only W which was not in the table array was not found and hence we get # N/A in place of value.
This was for exact match, for getting the approximate value we need to use True instead of Zero/False in the function. Using True will give us the closest match based on table array.
Hope the post will assist you all!!
Happy reading!!!
Basics: Vlookup (vertical lookup) and Hlookup (Horizontal lookup) is a function in excel which allows you to search / transfer data column wise and row wise respectively between two values / tables.
Since Vlookup is the most commonly used function in excel here in this post we will concentrate only on Vlookup.
Lookup_value: The value we want to search
Table_array: table in which we want to search (rem the value we are looking for must be the first column of the table)
Col_index_num: its column no. from left to right starting from 1st column of the table_array
Range_lookup: defines if we want an exact match or an approximate match (we will discuss both)
Example: We have 10 students with their marks in value as well as percentage
Now in another table we want only marks for 6 pre-selected students. In this case we can easily use vlookup for extracting the data from the database / table_array
In the first function we had find B in the table_array marked in Red. Since we need marks/ 3rd Column will be the index from left to right staring from 1st column of the table array. There after putting Zero / False will help you get the exact value or else you will get #N/A just in the 6th Case were W was not found
As you copy the formula in below cells for getting the data the table array shift too. This may give you wrong results In this case we have A in table array still the formula could not search the same. To avoid this most common mistake please make sure that table_array in formula is grouped with $ sign (select only table_array in formula and press F4) as per below table.
There after the final result that we get looks like this. Only W which was not in the table array was not found and hence we get # N/A in place of value.
This was for exact match, for getting the approximate value we need to use True instead of Zero/False in the function. Using True will give us the closest match based on table array.
Hope the post will assist you all!!
Happy reading!!!
No comments:
Post a Comment