Excel Tips No 30 : Searching and Fill matching data automatically

This free microsoft excel training tips no 30 is telling how to Search and Fill matching data automatically. See the Figure below. We have 2 Table : Table A and Table B. We need to complete information in column D of Table A : "Year of Join". Unfortunately, information about "Year of Join" is provided in Table B, with the same/reference data is Personal Number. In the manual way, we can complete this by finding cell by cell Years of Join base on the same data, ie Personal Numbers.
Microsoft Excel provide simple way to do this ...



In This case, we will use vlookup Function


1. Click cell that will be filled with data, ie : D5 Cell
2. Click Formula menu fx and choose vlookup function (This function is in lookup group function)
3. Look up Menu Windows will be appear

4. On Look up value click red arrow, beside blank field. Click with reference data on table A (ie Personal Number). This data must exist in both Table (Table A & B). ie Cell B5. You can also fill this field by Typing reference cell, ie B5.

5.On Table array, click red arrow beside blank field, Block column from column which contain refence information (Personal Number) until column which contain information that will be pick up (Year of Join) on Table B (ie from column G to H).

6. On Collum_index_number, filled with column number of column that contain information will be pick up on Table B(column G,Personal Number ), Counting start from Reference Column in Table B (Column H, Year of Join). So in this case Column G is number 2 if counted start from column G. Filled Collum_index_number by 2.

7. On Ranged_Look_Up field, filled with: 0

8. Copy formula on D5 to all cell that will be filled on "year of Join" Column in table A.

9. It's already Finished

Table B can be a table from the same sheet, different sheet or different file.
It's important to copy - paste special the result if table B is not coming from the same file, Because it can make the result become error if the reference does not opened, and you choose update table when you open the file.

Formula also make your file become larger in size, and more time in loading the file.
So, if you do not need to make auto update, copy - paste special the result and delete the colum that contain formula to avoid this.

May this tips can be useful for you