Assume that you have five hundred employee names in column A and their salaries in column B.

Your boss has sent a workbook containing one hundred names out of those five hundred names. He wants you to get their salaries from the table of five hundred people.

Using Ctrl F is a big no-no because it involves following steps for each employee:

1. Go to the cell containing first employee name sent by your boss.
2. Ctrl C to copy the cell.
3. Go to the data table of five hundred employees.
4. Ctrl F to open Find & Replace dialog box.
5. Ctrl V to paste the copied name.
6. Hit enter or press Ok.
7. Excel will take you to the desired cell.
8. Select the adjacent cell containing the salary amount.
9. Copy that amount using Ctrl C.
10. Go back to the data sent by your boss.
11. Paste the salary for the first employee.
12. Then repeat above 11 steps 99 more times to find and copy & paste the required salaries.

Now let's discuss about filtering option.

1. You apply filter on data set containing five hundred employees.
2. You uncheck all the filtered names.
3. You go back to boss's workbook.
4. You read the first name.
5. You come back to actual data workbook.
6. You select that name in the filter.
7. You again go back to your boss's workbook.
8. You read second employee name.
9. You come back to original data workbook.
10. You apply your second filter.
11. You need to follow steps 7-10 for ninety-eight more employees to filter all one hundred employees.
12. Now you have your one hundred names correctly filtered in actual data workbook.
13. You copy first salary from the filtered data.
14. You go back to boss's workbook.
15. You search the employee's name whose data has been copied.
16. You paste the first salary.
17. You go back to actual data workbook.
18. You copy next salary.
19. You go back to boss's workbook.
20. You search the employee's name again.
21. You paste the salary.
22. Then you repeat steps 17 to 21 for ninety-eight more employees.

Your life is little easy if you are allowed to sort both workbooks first. If your boss doesn't allow you to sort his data, then you are in above mess.

Using VLOOKUP will be a great time saver. You just apply VLOOKUP formula in the first cell of boss's workbook and fetch the salary. It hardly matters that boss's data is sorted or not.

Then you drag the first formula downwards to fetch ninety-nine more salaries in a matter of second.

See, how simple your life has become by using VLOOKUP function.

Homepage
​MS EXCEL TRAINING​​​