VLOOKUP Pulls Incorrectly

[Thomas [PBD]]

Hello all,

I have a tedious problem that is plaguing my workplace (not only myself, but
other coworkers as well). I must say that I am well versed in lookups and
excel criteria, as well as coding and troubleshooting, but I am out of ideas
for solving this specific issue as I cannot find the issue itself.

I have a sheet (Variance) which uses vlookups to pull information from
another sheet (Actual Hours), using a code below, in a named range
(Actual_Hrs). This works seemingly well about 90% of the time, until I run
into the problem where the "Department" does not exist in the named range
(which the code will force a zero value), however the value returned is for
that of a different "Department" that is similar in all aspects but the last
digit.
So,
Variance TAB VLOOKUP:
25042610001 =IF(ISERROR(VLOOKUP(A1,Actual_Hrs,3)),0,VLOOKUP(A1,Actual_Hrs,3))
25042610002 =IF(ISERROR(VLOOKUP(A2,Actual_Hrs,3)),0,VLOOKUP(A2,Actual_Hrs,3))
25042610003 =IF(ISERROR(VLOOKUP(A3,Actual_Hrs,3)),0,VLOOKUP(A3,Actual_Hrs,3))

The department 25042610001 exists, pulling the correct value, but
25042610002 and 25042610003 DO NOT exist, and therefore should return a 0,
but instead are returning the values of 25042610001.

Actual Hours TAB (named range Actual_Hrs) shows:
25042610001 87.72

Variance TAB values returned from VLOOKUP:
25042610001 87.72
25042610002 87.72
25042610003 87.72

I have tried using features such as changing to numbers, text, currency, etc
and formula modifications such as TEXT(), VALUE(), LEFT(), RIGHT(), TRIM(),
etc. and anything else to pull the correct information, yet it still returns
the value of a different department.
I believed at one point that the issue could lie in the value of the
"Department" because when the column width is not exact to fit the cell, the
department code returned is 2.5043E+10 and therefore could only see the first
5 digits, but still the issue persists when TEXT is forced.

Any help or comments will be greatly appreciated. Also, if example books
will need to be provided, I should be able to work on providing one.

 

[JE McGimpsey]

Set the range_lookup parameter to FALSE

(XL Help, "VLOOKUP":

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

will explain)

 

[T. Valko]

Since you've omitted the 4th argument if an exact match is not found VLOOKUP
returns the closest match that is less than the lookup value.

The department 25042610001 exists...but
25042610002 and 25042610003 DO NOT exist
but instead are returning the values of 25042610001.

Use the 4th argument and set to either 0 or FALSE:

=IF(ISERROR(VLOOKUP(A1,Actual_Hrs,3,0)),0,VLOOKUP(A1,Actual_Hrs,3,0))

=IF(ISERROR(VLOOKUP(A1,Actual_Hrs,3,FALSE)),0,VLOOKUP(A1,Actual_Hrs,3,FALSE))

 

[Thomas [PBD]]

Well.... look at me with the egg on his face. :-|
Figured I should have seen that my VLOOKUP didnt have false behind it, guess
I just assumed that I had it there because I never leave it off.
Thanks for your help. Quite appreciated.

 

[T. Valko]

You're welcome. Thanks for the feedback!