Anyone who wants to get expertise in Excel, VLOOKUP is an essential function for those which can easily make you awesome in excel.
There are two kind of lookups in excel one is VLOOKUP and second is HLOOKUP. Both works on same methodology with small difference that VLOOKUP works vertically (in Columns) and HLOOKUP works horizontally (in Rows). That’s why these are called Vertical Lookups and Horizontal Lookups respectively.
Here we are going to talk about VLOOKUP. There are many ways to use VLOOKUP which we will discuss with different methods and examples. Please follow our blog to become an expert of VLOOKUP
VLOOKUP is a vertical lookup which helps the user to extract the values from other columns (leftmost) basis on matching column string. Below is an image to explain you the same.
So in above example, we will get the Patient ID, Date of Birth, Site by using VLOOKUP Function for patient name mentioned in Cell H6
Now while looking at above Formula, there are four parameters which are described as below:
lookup_value: this is a matching value which we will find in our database to locate the position and get respective values from different columns from the same position
table_array: this is our database where we will find our matching value and get the respective left most column value
col_index_num: this is a column position or column address from where we need the value depending on the matched string of different column
[range_lookup]: here will be instructing the excel whether to find approximate match for the given lookup value or to find exact match
So here we have an example in Cell H6 “Martha Achock” and will get the Patient ID, Date of Birth, Site in H7,H8,H9 respectively. Below are the formulas which we need to write for getting these values:
Cell H7: “=VLOOKUP($H$6,$A$1:$E$17,2,0)”
Cell H8: “=VLOOKUP($H$6,$A$1:$E$17,3,0)”
Cell H9: “=VLOOKUP($H$6,$A$1:$E$17,4,0)”
This will get the respective VLOOKUP value for given Lookup value from the table_array.
That’s how you can use VLOOKUP for standardizing big databases and linking the different excel files.
Subscribe Our Blog to get more updates on Vlookup trick in Excel.
This tutorial explains how the TRANSPOSE function works and shows you the right way to use it to switch data in Excel.
Everyone has different preferences, even for work habits. Some people like to arrange data in vertical columns, while others prefer horizontal rows. If you ever need to switch the direction of your data quickly, the TRANSPOSE function can help
SUBSTITUTE function is used to substitute the existing old text to new text.
This guide shows how to use the nested IF function in Excel to check several conditions. You will also learn about other functions that can be to use than a nested formula.
When you want to make decisions in Excel, you often use an IF formula. It checks if something is true, then gives one result if it is and another result if it isn’t. If you need to check more than one thing, you can put many IFs inside each other.
Although using multiple IFs is common, it’s not the only way to check several conditions in Excel. This guide will introduce you to some easier and useful alternatives.
The tutorial explains what the Compound Annual Growth Rate (CAGR) is and shows how to create a clear and easy-to-understand formula for calculating CAGR in Excel.
ROUND function rounds the number value to nearest digit mentioned in argument.
ROUND function has two required arguments i.e. number and num_digits
SMALL function is used to get the Smallest k-th value from the range.
SMALL Function has two required arguments i.e. array, and k
Really this is very nice article…and this is easy to understand..