CODEWITHSUNDEEP
vbaexcelvlookup
MAK
MAK

Reputation: 7260

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.

Upvotes: 0

Views: 171

Answers (4)

Gravitate
Gravitate

Reputation: 3064

Old question, but for those seeing it now: Don't use VLOOKUP.

To my knowledge, there is NEVER a situation where VLOOKUP would be the best option.

INDEX/MATCH is better in literally every way.

  • INDEX/MATCH is faster. When you have hundreds or thousands of formulas, you can often speed up a workbook drastically simply by swapping out all of the VLOOKUPs.
  • INDEX/MATCH is more versatile. It can work on rows or columns and your lookup range can be anywhere. You can even lookup a value in a 2D table using INDEX/MATCH/MATCH.
  • INDEX/MATCH is more stable. Moving or re-ordering columns doesn't break your formulas.
  • INDEX/MATCH is easier to use. It may look confusing if you are used to VLOOKUP, but once you understand it, it is actually easier to use because you can directly reference the ranges, rather than counting the number of columns across.

So, the INDEX/MATCH version of the formula would be:

   =IFERROR(INDEX(Sheet1!$C$4:$C$15,MATCH(B4,Sheet1!$B$4:$B$15,0)),"")

Upvotes: 0

L S Perera
L S Perera

Reputation: 11

This is also good.

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

Upvotes: 0

ojars
ojars

Reputation: 1

=IFERROR(VLOOKUP(B4,Sheet1!A$4:B$15,2,0),"") can be reliably copied from cell to cell without shifting the array.

Upvotes: 0

IIJHFII
IIJHFII

Reputation: 600

try an exact match formula, so use:

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

Upvotes: 3

Related Questions