Easy way to learn VLOOKUP which is more essential during our analysis on comparing two or more tables.
For example, let us go by a real time scenario, In an Organization HR team want to know the employee work location.
They have two table named “Table1” and “Table2”.
HR want to add the work location in table1 from table2.
To compare two table VLOOKUP should have common field, here “EMP ID” is the common field.
Let’s start doing VLOOKUP to fulfill the HR requirement.
Step 1: Create a column called location in table1
Step 2: Type VLOOKUP formula in the cell “D3” under Location label
Step 3: select the first cell of the common field, Here EMP ID is the common field. Select the lookup_value cell “A3” and Type “,” to continue with formula
Step 4: This would be the critical step; we have to select the table where we are going to fetch the work location details.
Go to Work location table and select the label cell which is common to both the table, Here EMP ID is common field for both the table.
Drag and select the values of EMP ID for which you need work location details, extend till the work location column and known the column number to be entered in the formula
- The below picture shows selected columns 11R x 2C it means 11 Rows and 2 Columns, the formula automatically picks up the selected data.
- Now enter column number 2 in the formula as per below diagram, it means the data will be fetched from column 2
Step 5: Once you enter column number “2” follow with formula “,” and select “FALSE”. This option will only find the exact match from the common field.
Step 6: This would be the final step by completing formula close the formula with closing brace “)” and press enter, please find the complete formula below and comparison.
The Final output will be matching the work location to their respective employee.
NOTE: Please make practice of using the VLOOKUP when ever you do analysis of comparing two or more files which would be more effective formula in MS Excel.