Microsoft Excel is a spreadsheet program used to create grids of text, number and formulas specifying calculations. That’s extremely valuable for many businesses, which use it to record information regarding the business. If we have to find something specific in the vast spreadsheet which contains lots of information in it then what do we do? Nothing to worry because MS Excel has a special function known as VLOOKUP function. So, what does this function exactly do? This function searches for the value you specify and returns a matching value from another column.
Want to know why is it important to get your hands on MS Excel? Click here
VLOOKUP stands for “vertical lookup”. In Excel, this means the act of looking up data vertically across a spreadsheet, using the spreadsheets columns and a unique identifier within those columns as the basis of your search. When you look up your data, it must be listed vertically whenever that data is located. The formula always searches to the right.
Syntax: VLOOKUP (lookup_value, table_array, col_index_num,[range_lookup])
- Lookup _value: It is the value to search for.
- Table_array: Is two or more columns of data.
- Col_index_num: Is the number of the column from which the value should be returned.
- Range_lookup: This is optional; it determines whether to search for approximate or exact match.
How to use VLOOKUP in Excel?
- Organize the data
The first step to effectively use the VLOOKUP function is to make sure your data is well organized and suitable for using the function. VLOOKUP works in a left to right order, so you need to ensure that the information you want to look up is to the left of the corresponding data you want to extract.
Example: In the below VLOOKUP example, you will see that the “good table” can easily run the function to look up “Bananas” and return their price since Bananas are located in the leftmost column.
- Tell the function what to lookup
In this we start by typing the formula “=VLOOKUP (“and then select the cell that contains the information we want to lookup. In this case, it’s the cell that contains “Bananas”.
- Tell the function where to look
In this we need to select the table where the data is located, and search in the leftmost column for the information we selected in the previous step.
For example, we highlight the whole table from column A to column c. It will look for the information we told it to look up in column A.
- Tell Excel what column to output the data from.
In this we need to see which column contains the data that we want to have as an output from the VLOOKUP.
In the example, the output data is located in the 3rd column of the table, so we enter the number “3” in the formula.
- Exact or approximate match.
In this we need to specify if we’re looking for an exact or approximate match by entering “True” or “False”. In example we want an exact match (“Bananas”), so we type “false”. An approximate match it returns the next largest value that is less than your specific lookup value.
Wanna use MS Excel like a Pro?