How to use VLOOKUP formula in Excel?

What is VLOOKUP Formula?

VLOOKUP stands for "Vertical Lookup," and it is a function in Microsoft Excel. This Function is used to search for a value in the first column of a range or table and retrieve a corresponding value in the same row from another column. This formula is useful for quickly finding specific information in large data.

VLOOKUP Formula

=VLOOKUP(lookup value, table array, column index number, [range lookup])

VLOOKUP Parameter

Lookup Value - This is the term you want Excel to find.
Table Array - This is the range of cells where Excel will search for your desired information. Make sure the cell containing the value you're searching for is in the first column of this range.
Column Index Number - This is the position of the column in your selected range that holds the data you want Excel to find. For example, if your table spans A2, count column A as the first column, column B as the second, and so on. This number helps Excel locate and return the correct data from the specified column in your range.
Range Lookup - This option is used to decide whether Excel should find the exact value when using the VLOOKUP function.

How to do VLOOKUP in Excel with two spreadsheets (Sheet A & Sheet B)

In Sheet A, we have student information such as names, IDs, and courses. Sheet B contains a large dataset with student IDs and their corresponding percentages. Our goal is to match the student IDs from Sheet A with those in Sheet B to find and fill in the percentages for each student in Sheet A.

Step-by-Step Guide to Using VLOOKUP

Step 1 -

In Sheet A, go to the cell D1 in the percentage column where you want to enter or find data. Click on cell D1, then type "=VLOOKUP" and select VLOOKUP from the options that appear.

“=VLOOKUP( “

Step 2 -

In Sheet A, after you've entered "=VLOOKUP" and selected the VLOOKUP function in the Excel cell, the next step is to choose the Column B in Sheet A that you want to use for matching data from Sheet B.

“=VLOOKUP(B:B”

Step 3 -

In Sheet B, after completing the previous step, navigate to Sheet B and select Columns A:B . Column A is chosen because it contains the Student IDs that will be matched with Sheet A. In Column A of Sheet B, you'll find the Student IDs, while Column B contains the percentage values that we want to insert into Sheet A.

=VLOOKUP(B:B,'Sheet B'!A:B

Step 4 -

In Sheet B,

Enter column location 2 [=VLOOKUP(B:B,'Sheet B'!A:B,2]
Next Enter 0 [=VLOOKUP(B:B,'Sheet B'!A:B,2,0] to match the value exactly in Sheet B and return the corresponding value where needed in Sheet A.

=VLOOKUP(B:B,'Sheet B'!A:B,2,0

Final Result -

In the final result, you can see that we used the VLOOKUP function to fetch student percentage data and successfully filled in the percentage column in Sheet A with data from Sheet B.