VLOOKUP can match two tables and return to the values that share the same identifier. For example, Table with columns A – E and Table with columns I – J share the same variable ID. VLOOKUP function can search ID in column A from column I and return to a region if the two IDs match. If IDs don't match, the result returns "#N/A" (Download example here).
Step 1: Click the cell F2, the first cell where you want to have the Function (note, you can also insert a helper column between column A – E);
Step 2: Click the "Formula" Tab from the Ribbon;
Step 3: Click "Insert Function";
Step 4: In the Insert Function Window, type VLOOKUP in the box and click Go. VLOOKUP function will appear in the box below. Click VLOOKUP and then OK at the bottom;
Step 5: The Function Arguments window will appear. In the 1st box, click the cell with the lookup ID A2;
Step 6: In the 2nd box, select the data range $I$1:$J$31 where the lookup values are located;
Note: Please use the absolute value after selecting the range, and the search data range will be the same all the time.
Step 7: In the 3rd box, type in number 2 for Region. In the Table "I – J", column J is the 2nd column (please count the number of column from the identifier column);
Step 8: In the 4th box, type false for exact search;
Step 9: Click OK, F2 will show the value or "#N/A". When a value in column A (lookup value) cannot match the values in column I (search range), the result will return "#N/A"; however, it will show a value when the two columns match.
Step 10: Copy down the column: move your mouse to the right bottom corner of cell F2 till the mouse turns to a black cross (see below);
Step 11: Double click and you will have all the cells filled with matching value;
Step 12: Copy column F and paste special with value only to remove formula.