vlookup shows result one cell above the expected result
 

Hi there,
For some reason, my vlookup formula results in the match that appears a cell
above the correct match.
The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE)
My cells are fomatted to 'General' (not text or number). I've tried sorting,
but it did not fix the problem.
There are 5 columns on my worksheet (1st column shows asset ID, 2nd column
shows corresponding name of the asset, column 3 shows Available Units, column
4 shows the price). Column 5 (E) shows the asset ID's that I am finding a
match for.
I need to show (as a result) the Available Units once a match between ID's
is found.

Thanks for your help.
Marie

vlookup shows result one cell above the expected result
 

Are you wanting an *EXACT* match? If so, change the range_lookup argument to
FALSE or 0.

=VLOOKUP(E3,A1:D20,3,FALSE)
=VLOOKUP(E3,A1:D20,3,0)

Biff

"marie" wrote in message
...
Hi there,
For some reason, my vlookup formula results in the match that appears a
cell
above the correct match.
The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE)
My cells are fomatted to 'General' (not text or number). I've tried
sorting,
but it did not fix the problem.
There are 5 columns on my worksheet (1st column shows asset ID, 2nd column
shows corresponding name of the asset, column 3 shows Available Units,
column
4 shows the price). Column 5 (E) shows the asset ID's that I am finding a
match for.
I need to show (as a result) the Available Units once a match between ID's
is found.

Thanks for your help.
Marie

vlookup shows result one cell above the expected result
 

What happens if you change "True" to "False" (no quotes)?
--

Regards,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"marie" wrote in message
...
Hi there,
For some reason, my vlookup formula results in the match that appears a cell
above the correct match.
The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE)
My cells are fomatted to 'General' (not text or number). I've tried sorting,
but it did not fix the problem.
There are 5 columns on my worksheet (1st column shows asset ID, 2nd column
shows corresponding name of the asset, column 3 shows Available Units,
column
4 shows the price). Column 5 (E) shows the asset ID's that I am finding a
match for.
I need to show (as a result) the Available Units once a match between ID's
is found.

Thanks for your help.
Marie

vlookup shows result one cell above the expected result
 

You should only use VLOOKUP with a 4th argument of TRUE when you have lookup
range sorted ascending and you want to match with the nearest value below.

For your purposes I presume you need an exact match, change TRUE to FALSE

"marie" wrote:

Hi there,
For some reason, my vlookup formula results in the match that appears a cell
above the correct match.
The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE)
My cells are fomatted to 'General' (not text or number). I've tried sorting,
but it did not fix the problem.
There are 5 columns on my worksheet (1st column shows asset ID, 2nd column
shows corresponding name of the asset, column 3 shows Available Units, column
4 shows the price). Column 5 (E) shows the asset ID's that I am finding a
match for.
I need to show (as a result) the Available Units once a match between ID's
is found.

Thanks for your help.
Marie

vlookup shows result one cell above the expected result
 

When I changed the 4th argument to FALSE or 0, my result shows N/A.
I tried removing the 4th argument, but the result is the same as when I had
the 4th arg of TRUE in it.


"RagDyeR" wrote:

What happens if you change "True" to "False" (no quotes)?
--

Regards,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"marie" wrote in message
...
Hi there,
For some reason, my vlookup formula results in the match that appears a cell
above the correct match.
The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE)
My cells are fomatted to 'General' (not text or number). I've tried sorting,
but it did not fix the problem.
There are 5 columns on my worksheet (1st column shows asset ID, 2nd column
shows corresponding name of the asset, column 3 shows Available Units,
column
4 shows the price). Column 5 (E) shows the asset ID's that I am finding a
match for.
I need to show (as a result) the Available Units once a match between ID's
is found.

Thanks for your help.
Marie

vlookup shows result one cell above the expected result
 

This usually denotes that your data is not equal in both lists.

To test this, *manually* type in a value in Column E, and *manually* type in
the *exact* same value in Column A, so that you *know* that you have a
match.
Also, make sure both test cells are the same format.

What happens now?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"marie" wrote in message
...
When I changed the 4th argument to FALSE or 0, my result shows N/A.
I tried removing the 4th argument, but the result is the same as when I
had
the 4th arg of TRUE in it.


"RagDyeR" wrote:

What happens if you change "True" to "False" (no quotes)?
--

Regards,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"marie" wrote in message
...
Hi there,
For some reason, my vlookup formula results in the match that appears a
cell
above the correct match.
The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE)
My cells are fomatted to 'General' (not text or number). I've tried
sorting,
but it did not fix the problem.
There are 5 columns on my worksheet (1st column shows asset ID, 2nd
column
shows corresponding name of the asset, column 3 shows Available Units,
column
4 shows the price). Column 5 (E) shows the asset ID's that I am finding
a
match for.
I need to show (as a result) the Available Units once a match between
ID's
is found.

Thanks for your help.
Marie

vlookup shows result one cell above the expected result
 

I got it. I had the cells formatted the same way, but I tested it further.
Because the values on columns A and E had numbers that starts with '00', for
some reason, excel is not recognizing the first 2 digits once the formatting
has been changed to general from numeric.
Thanks for the help.

"Ragdyer" wrote:

This usually denotes that your data is not equal in both lists.

To test this, *manually* type in a value in Column E, and *manually* type in
the *exact* same value in Column A, so that you *know* that you have a
match.
Also, make sure both test cells are the same format.

What happens now?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"marie" wrote in message
...
When I changed the 4th argument to FALSE or 0, my result shows N/A.
I tried removing the 4th argument, but the result is the same as when I
had
the 4th arg of TRUE in it.


"RagDyeR" wrote:

What happens if you change "True" to "False" (no quotes)?
--

Regards,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"marie" wrote in message
...
Hi there,
For some reason, my vlookup formula results in the match that appears a
cell
above the correct match.
The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE)
My cells are fomatted to 'General' (not text or number). I've tried
sorting,
but it did not fix the problem.
There are 5 columns on my worksheet (1st column shows asset ID, 2nd
column
shows corresponding name of the asset, column 3 shows Available Units,
column
4 shows the price). Column 5 (E) shows the asset ID's that I am finding
a
match for.
I need to show (as a result) the Available Units once a match between
ID's
is found.

Thanks for your help.
Marie

vlookup shows result one cell above the expected result
 

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"marie" wrote in message
...
I got it. I had the cells formatted the same way, but I tested it further.
Because the values on columns A and E had numbers that starts with '00',
for
some reason, excel is not recognizing the first 2 digits once the
formatting
has been changed to general from numeric.
Thanks for the help.

"Ragdyer" wrote:

This usually denotes that your data is not equal in both lists.

To test this, *manually* type in a value in Column E, and *manually* type
in
the *exact* same value in Column A, so that you *know* that you have a
match.
Also, make sure both test cells are the same format.

What happens now?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"marie" wrote in message
...
When I changed the 4th argument to FALSE or 0, my result shows N/A.
I tried removing the 4th argument, but the result is the same as when I
had
the 4th arg of TRUE in it.


"RagDyeR" wrote:

What happens if you change "True" to "False" (no quotes)?
--

Regards,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"marie" wrote in message
...
Hi there,
For some reason, my vlookup formula results in the match that appears
a
cell
above the correct match.
The formula that I am using is: =VLOOKUP(E3,A1:D20,3,TRUE)
My cells are fomatted to 'General' (not text or number). I've tried
sorting,
but it did not fix the problem.
There are 5 columns on my worksheet (1st column shows asset ID, 2nd
column
shows corresponding name of the asset, column 3 shows Available
Units,
column
4 shows the price). Column 5 (E) shows the asset ID's that I am
finding
a
match for.
I need to show (as a result) the Available Units once a match between
ID's
is found.

Thanks for your help.
Marie