ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   VLOOKUP Pulls Incorrectly (https://www.excelbanter.com/excel-discussion-misc-queries/187126-vlookup-pulls-incorrectly.html)

Thomas [PBD]
VLOOKUP Pulls Incorrectly
 
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
VLOOKUP Pulls Incorrectly
 
Set the range_lookup parameter to FALSE

(XL Help, "VLOOKUP":

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

will explain)


In article ,
Thomas [PBD] wrote:

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.

T. Valko
VLOOKUP Pulls Incorrectly
 
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,VLOO KUP(A1,Actual_Hrs,3,FALSE))


--
Biff
Microsoft Excel MVP


"Thomas [PBD]" wrote in message
...
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.



Thomas [PBD]
VLOOKUP Pulls Incorrectly
 
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" wrote:

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,VLOO KUP(A1,Actual_Hrs,3,FALSE))


--
Biff
Microsoft Excel MVP


"Thomas [PBD]" wrote in message
...
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.




T. Valko
VLOOKUP Pulls Incorrectly
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Thomas [PBD]" wrote in message
...
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" wrote:

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,VLOO KUP(A1,Actual_Hrs,3,FALSE))


--
Biff
Microsoft Excel MVP


"Thomas [PBD]" wrote in message
...
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.


All times are GMT +1. The time now is 11:17 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com