Vlookup Formula in MS Excel
What is Vlookup ?
Vlookup formula is the option of vertical Lookup to get information from the table.There is a lot of data in a big table, and if we have to extract some information, we can easily see the information by applying Vlookup.
How can vlookup be used?
Not only information, but as a software, we can use it. Like the data of the student. From that, we have to extract the class and contact information of a student, then we can get information in 1 second by just entering the student’s name or code from the option.
1) View Lookup Value or Find List Information
The screen shot Table below contains the serial number, name, and phone number. We will type his name in one cell and find his mobile number in another cell.
Let’s learn from the step how to apply vlookup…..
Mobile number has to be brought from the table C2:D22 in cell G6 using cell F6. For vlookup formula we need 4 things.
1. Lookup Value
2. Table Array
3. Column index number
4. Range Lookup
1. Lookup Value – The criterion from which we are searching for information is called Criteria Lookup Value.We will use the name here to find the mobile number. So the lookup value will be the name cell F6 you find here.
2. Table Array – The table from which we are searching for information is a table Array. In this example, the table from name to mobile number is for us Table Array. If we were searching from serial number then our table would be array from serial number to mobile number.
Table Array -C2:D22
3. Column index number – The information that you are searching for in the table array is the number column in which the column is index number. In our table array, the column name is 1st and the mobile number is 2nd. When we are searching for mobile numbers, the column will be index number 2. If you search through serial number, the mobile number will go to number 3.
4. Range Lookup – For Exact result, we have to type false or 0. For Approximate result, type True or 1. We are searching for a number named Exact here, so we will type 0.
In short, we will give the command to Excel, show us Mobile Number from the table named toyo.
Type in cell G6 =Vlookup( Then Highlights of the formula will be seen. The value to be applied will be bold.
Click cell F6 of lookup value and give commas. Select the table Array after the comma, C2: D22. Then give a comma. Type Lookup value Column Index number is 2. Type Range_lookup 0 and close the bracket. Press enter.
=VLOOKUP(F6,C:D,2,0)
The mobile number of the name you will type in F4 cell will appear.
If you apply the formula once, you can copy it in the cell below and bring multiple results. But the table array selected will also go down with copy paste and Vlookup will not work. Therefore, while selecting the table array, we will not select the data but select the column.
Now copy the formula and paste it in as many cells as you want. All Cell Results will be found in a Copy Paste.
The result given below is the serial number in the example given below. In it, only the table is selected according to the serial number. The column index number changes accordingly.
The Vlookup result is obtained by copy paste in the cell below the formula in the same column. But if you want to copy paste the formula from the cell of one column to the cell of the next column, then you can do it in the following way. After applying the formula, click on that cell and press F2. Select the formula. And just click and paste in the cell next to it. In the formula, only the columns change according to the index number value.
2) Make Information Form
You can create an information form or ledger using Vlookup. Prepare the information form and show every field information from Vlookup. Here the information is much easier according to the code number.
We can apply another way to select the table array here.You can also select the blank row below the data more than you have so that more data can be shown on entering below. Or you can keep the data separately and form in separate sheet also.
Select the data list. Click on the name box on the left of the address bar and give any one name to the data list. Enter after typing the name. Type the same name in the table array of Vlooup.
3. Automate name and price according to code when entering data –
Selecting the table of Code, Item Name, and Price, give a name in the Name box as mentioned above.
Where data entry is to be done, apply Vlookup formula according to the code in the item name and price column. When you type the code, the automatic table will search by item name and price. Now you just have to type the code.
4 ) Cross-check two list –
If we have 2 lists. It has some value missing. We can find which value or name is missing through Vlookup. In the following example, Vlookup is to be placed in front of the name of List 1. The table will be Array List No 2. The name of the first list is present in the second list or not, it will be seen from Vlookup. Column index number 1 has to be given. In the same way, giving the Array of the first list in front of the second list, the name in the first list will not be seen.
#N/A will be shown in front of the name which is not available. By applying such cross-formula, we can cross-check the list.
Important –
Vlookup Formula, we can apply data of different sheet.
Vlookup Formula works in different workbooks also.
Vlookup formula
Only the column next to lookup value gives the result of the number.
If you put Vlookup Formula with the name, then the spelling of both the names should be same. If something is different in spelling or there is also a difference of space special character, then the result will not be available.