What is VLOOKUP: –
In VLOOKUP, V stands for vertical and with the help of this formula, we can retrieve the matched value from the selected cells(table array) from left to right means in vertical order. It supports approximate and exact matched value. It can only look up and retrieve the values in the vertical order. The lookup value must be in the first column in the selected cells(table array).
Need for VLOOKUP formula: –
- With the help of VLOOKUP, we can retrieve the numbers of exact or approximate matched value in the seconds.
- It helps in saving time and doing work more efficiently.
- it will show the matched value in the column/row where you want. So, we do not need to find the values individually from the selected date and then copy, paste it.
The Feature of VLOOKUP formula: –
- It works only left to right. It can only look up the value from the Selected cell(Table_array) from left to right side.
- This formula provides two types of matched value i.e. approximate and exact.
- VLOOKUP always finds the first matched value from the selected cell(Table_array) and retrieve it.
- We can use 1 and 0 instead of True and False respectively.
- It retrieves data based on column number.
Explanation of Formula:-
Now, We will explain the Arguments of the formula.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value: – The lookup value is that value, which will find the exact matched value from the selected cells(table array) with this formula. This value is always written in the first column of the selected cells(table array) because VLOOKUP only finds the value in the vertical order from the selected cells.
table_array: – This is the array of selected cells from which the VLOOKUP will find and retrieve the matched value.
col_index_num: – It means Column Index Number. The number of that column from which we want to retrieve the matched value. The number of columns is counted from the first column of the selected cells(table array).
range_lookup: – It means what type of match you want to show. There are two types of match shown as following: –
- Ture: – Approximate Match
- False: – Exact Match
If you want to retrieve the Exact match value to show the write the false and if you approximate match then write true.
Example of Formula: –
In the following table, we want to retrieve the predetermined percentage of increment from the table according to the appraisal score.
We will use the VLOOKUP formula to retrieve the predetermine the percentage of increment according to the appraisal score.
1st Step: –
We will write the “=VLOOKUP( ” in the column of the percentage of increment.
2nd Step: –
Select the value which we common in both table i.e. Appraisal Score and add a comma at the end.
3rd Step: –
Select the Table array from which we want to retrieve the value and add a comma at the end.
4th Step: –
Fix the selection of table array by adding the dollar sign ($) in both the starting and end of the selection like shown below: –
This step is needed where we did not want to update the selection of the table array when we copy the whole formula in the remaining cell.
5th Step: –
Now select the number of the column of the selected table array of which the value you want to retrieve.
6th Step: –
Now choose the mode of matching if you want to match exact value then enter false or 0 or if you want to match approximate value then enter true or 1.
Now, the formula is complete and you will get the result.
Step No. 7: –
So copy this result(means formula) and then paste it in the remaining cell in which you want to use the same.
Please Share and comment your feedback in the comment box.
to buy the Microsoft Excel Click Here.
Also, Check out the following function of excel: –
- HLOOKUP in Excel – Explanation with example
- IF Function in Excel – Explanation with example
- Excel Index Function – Download Practice Sheet
- Excel Match Function- Download practice Sheet
- Excel SUMIF Function – Download practice Sheet
- COUNTIF Excel Function – Download Practice Sheet