The VLOOKUP function (which stands for Vertical Lookup), is used to find specific information that has been stored in a spreadsheet table. In the example below, we are going to use an employee number to lookup the corresponding first name in another part of the workbook.
Below is the spreadsheet page we wish to complete (called Sheet 3).
The corresponding First Name and Last Name information is stored in a table on another page of the spreadsheet called ‘Data’, and looks like this:
First of all, we want to find the matching First Name, which we are going to bring across from the ‘Data’ page.
Click in Cell B2 and select ‘Insert Function’
Ensure that ‘select a category’ is set to ‘All’. Scroll through the functions to select ‘VLOOKUP’ (as below), then click ‘OK’.
Now, we need to enter the function arguments, as below:
Lookup_value = A2 (because A2 is where the employee number is displayed, and that’s the common data that matches between the two tables we are working with in this spreadsheet).
Table_array = Data!A2:C7 (because this is the cell range where the source data is stored on the page called ‘Data’).
Col_index_num = 2 (because it’s the 2nd column of the table on the Data page that actually contains the First Name information that we want to bring across)
Range_lookup = FALSE (because we want to find an EXACT match)
As you can see, this formula has worked. The Employee No. 1234 belongs to ‘Anna’ and this First Name information has successfully been pulled across from the page called ‘Data’, as the employee number 1234 matches up correctly.
Now, before we can copy the function/formula down to the cells below it, we need to make the formula ‘absolute’ by inserting the $ symbol.
The formula is currently: =VLOOKUP(A2,Data!A2:C7,2,FALSE)
The formula should be changed to: =VLOOKUP(A2,Data!$A$2:$C$7,2,FALSE), the amendments can simply be made in the formula bar by manually typing the $ symbol in the correct place.
Finally, you can copy this formula now to the remaining cells in Column B.