The formula for a vlookup is:
=vlookup(lookupvalue, table_array, col_index_num, true/false)
Lookupvalue = The cell you are using as an index between the two sources of data. You must pick the cell in the row you are entering this formula (often A2).
Table_array = Highlight of the area of the informaton you will be pulling into the new spreadsheet (ie the target data). You must highlight the columns (eg A:B) rather than the specific cells (eg. A1:B2).
Col_index_num = How many columns along in the table_array the specfic cell you want to pull is. This will be a number greater than 1. You have to manually count the number of columns along. Note in the example below the table array would be B:F and we want to pull in column F, F is the 5th column along in the table array. So we would enter 5 in this case.
True / False = If False then the Lookupvalue must match perfectly between the two spreadsheets for it to work. If True then Excel will pull fields that are similar. It is generally recommended to put False here.
After each segment has been completed press comma (,) to move to the next segment.
In this example we want to pull the values on the left into the spreadsheet on the right.
1) Lookup_value = ‘A2’ – This is the common field between the two sets of information that will be used as the key on the relevant line on the same spreadsheet where we are entering the formula.
2) Table_array = ‘[Workbook2]Sheet1$A:$B’ – This is the target information. Rather than typing this field in we simply highlighted columns A and B on the left hand spreadsheet then press comma.
3) Col_index_num = ‘2’ – We simply enter 2 then comma. This is because Value is two columns along on the left spreadsheet.
4) True/false = ‘false’ – We simply type false then close brackets. False as we only want exact matches to be pulled to the right hand spreadsheet.
We can now copy and paste this formula down to cover the full spreadsheet.