Search code examples
vbaexcelvlookup

Excel 2010: VLOOKUP Function

I have two sheets namely:

  1. Sheet1
  2. Sheet2

The following data which sheets contain.

Sheet1:

  A         B           C            D
-----------------------------------------
Emp_ID  Emp_Name    Str_Date    End_Date
1         EmpA      1/1/2010    1/1/2011
1         EmpA      1/2/2010    1/2/2011
1         EmpA      1/3/2010    1/3/2011
1         EmpA      1/4/2010    1/4/2011
2         EmpB      1/5/2010    1/5/2011
2         EmpB      1/6/2010    1/6/2011
2         EmpB      1/7/2010    1/7/2011
2         EmpB      1/8/2010    1/8/2011
3         EmpC      1/9/2010    1/9/2011
3         EmpC      1/10/2010   1/10/2011
3         EmpC      1/11/2010   1/11/2011
3         EmpC      1/12/2010   1/12/2011

Sheet2:

   A      B        C
------------------------
Dept_ID Emp_ID  Emp_Name
111     1   
222     2   
333     3   
444     4   
555     5   
666     6

Note: I want to print the employee name in the second sheet which should match the employeeID from sheet1. If the employeeID doesn't match then it should print empty cell.

My try:

I have written the following VLOOKUP Function in the sheet2 below the column Emp_Name.

Function:

=IFERROR(VLOOKUP(B4,Sheet1!A4:B15,2),"")

Getting Output:

   A      B        C
------------------------
Dept_ID Emp_ID  Emp_Name
111     1       EmpA
222     2       EmpB
333     3       EmpC
444     4       EmpC
555     5       EmpC
666     6       EmpC

Expected Result:

   A      B        C
------------------------
Dept_ID Emp_ID  Emp_Name
111     1       EmpA
222     2       EmpB
333     3       EmpC
444     4       
555     5       
666     6

Note: In the expected result the employeeID 4, 5, 6 don't have entries in the sheet1 so the result should be empty cell.

Solution

  • try an exact match formula, so use:

    =IFERROR(VLOOKUP(B4,Sheet1!A4:B15,2,0),"")