A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Wrong results from MATCH function



 
 
Thread Tools Display Modes
  #1  
Old June 22nd, 2009, 01:05 AM posted to microsoft.public.excel.worksheet.functions
Imladrian
external usenet poster
 
Posts: 1
Default Wrong results from MATCH function

Softwa Excel 2007 SP1

I'm using the FV function to compare future returns on investments. The
results of the six scenarios in row 36 are as follows:

C:36 $606,174.11
D:36 $549,893.90
E:36 $606,776.86
F:36 $531,553.59
G:36 $129,076.61
H:36 $121,795.72

In J:36 I have the function "=MAX(C36:H36)" which correctly outputs
"$606,776.86" as the largest value.

In K:36 I have the function "=MATCH(J36,C36:H36)" which correctlyl outputs
"3" as the result.

Below are the results of the same scenarios in row 37 (one additional
payment/investment period):

C:37 $609,686.10
D:37 $551,850.46
E:37 $608,361.51
F:37 $532,382.33
G:37 $133,293.94
H:37 $125,276.10

The same functions were included in columns J and K. The MAX function in
J:37 outputted the correct result: $609,686.10 (column C). However, the
MATCH function in K:37 incorrectly outputted "6" as the result.

On all subsequent rows column "C" has the highest return, with the MAX
function giving me the correct result and the MATCH function returning "6" to
me. The same thing happens whether I use the FV function in columns C-H or
copy and paste just the values; the MATCH function gives me bad information.
I've included the results from rows 36 & 37 below for ease of 'copy and
paste'.

Row 36:
$608,524.17 $551,203.14 $607,837.23 $532,108.14 $131,877.65 $124,107.29

Row 37:
$609,686.10 $551,850.46 $608,361.51 $532,382.33 $133,293.94 $125,276.10

Any help would be greatly appreciated!
  #2  
Old June 22nd, 2009, 01:41 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Wrong results from MATCH function

You should use an EXACT match,
ie with the match type parameter set to FALSE or zero

Instead of in K36: "=MATCH(J36,C36:H36)"
Use in K36: =MATCH(J36,C36:H36,0)

voila? punch it here, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Imladrian" wrote:
Softwa Excel 2007 SP1

I'm using the FV function to compare future returns on investments. The
results of the six scenarios in row 36 are as follows:

C:36 $606,174.11
D:36 $549,893.90
E:36 $606,776.86
F:36 $531,553.59
G:36 $129,076.61
H:36 $121,795.72

In J:36 I have the function "=MAX(C36:H36)" which correctly outputs
"$606,776.86" as the largest value.

In K:36 I have the function "=MATCH(J36,C36:H36)" which correctlyl outputs
"3" as the result.

Below are the results of the same scenarios in row 37 (one additional
payment/investment period):

C:37 $609,686.10
D:37 $551,850.46
E:37 $608,361.51
F:37 $532,382.33
G:37 $133,293.94
H:37 $125,276.10

The same functions were included in columns J and K. The MAX function in
J:37 outputted the correct result: $609,686.10 (column C). However, the
MATCH function in K:37 incorrectly outputted "6" as the result.

On all subsequent rows column "C" has the highest return, with the MAX
function giving me the correct result and the MATCH function returning "6" to
me. The same thing happens whether I use the FV function in columns C-H or
copy and paste just the values; the MATCH function gives me bad information.
I've included the results from rows 36 & 37 below for ease of 'copy and
paste'.

Row 36:
$608,524.17 $551,203.14 $607,837.23 $532,108.14 $131,877.65 $124,107.29

Row 37:
$609,686.10 $551,850.46 $608,361.51 $532,382.33 $133,293.94 $125,276.10

Any help would be greatly appreciated!

  #3  
Old June 22nd, 2009, 01:41 AM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme[_2_]
external usenet poster
 
Posts: 1,027
Default Wrong results from MATCH function

You have not used the third argument with MATCH. When this is omitted the
table being looked must be sorted (ie: in order) It just happened to give
you the right result in the first set of data

Use "=MATCH(J36,C36:H36,0)" ----- the 0 stipulates you want an exact match
and it must be used then the table is unsorted.

If you search Help with the word "MATCH" you will be able to confirm what I
am telling you

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Imladrian" wrote in message
...
Softwa Excel 2007 SP1

I'm using the FV function to compare future returns on investments. The
results of the six scenarios in row 36 are as follows:

C:36 $606,174.11
D:36 $549,893.90
E:36 $606,776.86
F:36 $531,553.59
G:36 $129,076.61
H:36 $121,795.72

In J:36 I have the function "=MAX(C36:H36)" which correctly outputs
"$606,776.86" as the largest value.

In K:36 I have the function "=MATCH(J36,C36:H36)" which correctlyl outputs
"3" as the result.

Below are the results of the same scenarios in row 37 (one additional
payment/investment period):

C:37 $609,686.10
D:37 $551,850.46
E:37 $608,361.51
F:37 $532,382.33
G:37 $133,293.94
H:37 $125,276.10

The same functions were included in columns J and K. The MAX function in
J:37 outputted the correct result: $609,686.10 (column C). However, the
MATCH function in K:37 incorrectly outputted "6" as the result.

On all subsequent rows column "C" has the highest return, with the MAX
function giving me the correct result and the MATCH function returning "6"
to
me. The same thing happens whether I use the FV function in columns C-H
or
copy and paste just the values; the MATCH function gives me bad
information.
I've included the results from rows 36 & 37 below for ease of 'copy and
paste'.

Row 36:
$608,524.17 $551,203.14 $607,837.23 $532,108.14 $131,877.65 $124,107.29

Row 37:
$609,686.10 $551,850.46 $608,361.51 $532,382.33 $133,293.94 $125,276.10

Any help would be greatly appreciated!



  #4  
Old June 22nd, 2009, 07:32 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Wrong results from MATCH function

If the third argument of MATCH() is omitted, it is assumed to be 1. If
match_type is 1, MATCH finds the largest value that is less than or equal to
lookup_value and so to return the correct value the lookup values should be
in sorted order.....If match_type is 0 MATCH finds the first value that is
exactly equal to lookup_value and the array can be in any order.

If this post helps click Yes
---------------
Jacob Skaria


"Imladrian" wrote:

Softwa Excel 2007 SP1

I'm using the FV function to compare future returns on investments. The
results of the six scenarios in row 36 are as follows:

C:36 $606,174.11
D:36 $549,893.90
E:36 $606,776.86
F:36 $531,553.59
G:36 $129,076.61
H:36 $121,795.72

In J:36 I have the function "=MAX(C36:H36)" which correctly outputs
"$606,776.86" as the largest value.

In K:36 I have the function "=MATCH(J36,C36:H36)" which correctlyl outputs
"3" as the result.

Below are the results of the same scenarios in row 37 (one additional
payment/investment period):

C:37 $609,686.10
D:37 $551,850.46
E:37 $608,361.51
F:37 $532,382.33
G:37 $133,293.94
H:37 $125,276.10

The same functions were included in columns J and K. The MAX function in
J:37 outputted the correct result: $609,686.10 (column C). However, the
MATCH function in K:37 incorrectly outputted "6" as the result.

On all subsequent rows column "C" has the highest return, with the MAX
function giving me the correct result and the MATCH function returning "6" to
me. The same thing happens whether I use the FV function in columns C-H or
copy and paste just the values; the MATCH function gives me bad information.
I've included the results from rows 36 & 37 below for ease of 'copy and
paste'.

Row 36:
$608,524.17 $551,203.14 $607,837.23 $532,108.14 $131,877.65 $124,107.29

Row 37:
$609,686.10 $551,850.46 $608,361.51 $532,382.33 $133,293.94 $125,276.10

Any help would be greatly appreciated!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:26 AM.


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