VLOOKUP returning incorrect values

Question

On Sheet1 I have a list of names in columnA.  On Sheet2, I have a list of names in Column A, Column B is sick time, Column C is Vacation time, Column D is department.

I have this formula =VLOOKUP(A54,Sheet2!A:D,4,FALSE) in a cell on Sheet1 to pull the dept to Sheet 1.  For people with the same last name, it is pulling the incorrect department.  I am using the same formula to pull the sick time and vacation time but it is pulling correctly.  Why won't it pull the department correctly as well?  Anybody know how to correct it?

Example:

Sheet 1

Name                     Sick           Vac            Dept

Torres, David          16.2           12             Payroll (should be Music)

Sheet 2

Name                       Sick             Vac           Dept

Tinker, Troy               1                  2                 Post Office

Torres, Sheila           3.2              4.2             Payroll

Torres, David           16.2           12               Music

Answer 1

Daniel wrote:

I have this formula =VLOOKUP(A54,Sheet2!A:D,4,FALSE) in a cell on Sheet1 to pull the dept to Sheet 1.  [....] Why won't it pull the department correctly as well?  Anybody know how to correct it?

Example:

Sheet 1

Name                     Sick           Vac            Dept

Torres, David          16.2           12             Payroll (should be Music)

 

Sheet 2

Name                       Sick             Vac           Dept

Torres, Sheila           3.2              4.2             Payroll

Torres, David           16.2           12               Music

My guess:  even though you wrote VLOOKUP(...,FALSE) in your posting, you actually have VLOOKUP(...,TRUE) or you are missing the 4th parameter (defaults to TRUE) in your worksheet.  Did you copy-and-paste the problematic formula from the Formula Bar into your posting, or did you retype here?

As you may know, if you use VLOOKUP(...,TRUE) with a lookup table that is not in ascending order, the results are unreliable.  Sometimes they work; sometimes they return an Excel error; and sometimes they simpy return the wrong result.

Answer 2

That is odd. As you say, it's working correctly for the other columns. Structurally, your formula looks correct. Obvious question, is the entire name (last and first) in one cell? Is the workbook in Manual calculation mode?

For the "not sure why it doesn't work, try something else" approach, another formula (and some would argue more efficient) you could use is:

=INDEX(Sheet2!D:D,MATCH($A54,Sheet2!$A:$A,0))

Note that this formula can still be copied across/down as needed to adapt to your different columns. The benefit, is that you don't need to change a column # call-out, and it's less memory usage than VLOOKUP (I think).

Answer 3

Are the last name and first names in single cells? 

If so, then it should work.

Are you sure A54 has "Torres, David" and not "Torres, Sheila"... i.e are you referencing the correct cell in the VLOOKUP?  You might be off by one cell or something...