vlookup shows result one cell above the expected result

[Guest]

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,A120,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

 

[Biff]

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

=VLOOKUP(E3,A120,3,FALSE)
=VLOOKUP(E3,A120,3,0)

Biff

 

[RagDyeR]

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

Regards,

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

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,A120,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

 

[Guest]

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

 

[Guest]

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]

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?

 

[Guest]

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]

Appreciate the feed-back.