Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default 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





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default 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





  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
The formula shows in cell but not the result Susan Excel Discussion (Misc queries) 1 November 9th 06 04:05 PM
Cell shows formula eg ='Sheet21'!F3 instead of result mikehutt Excel Worksheet Functions 1 September 20th 06 05:27 PM
Formula result as real empty/blank cell Excelerate-nl Excel Worksheet Functions 4 August 20th 06 06:52 AM
Sum formula result shows "#N/A" Dinesh Excel Worksheet Functions 3 August 19th 06 04:03 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 02:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"