Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Getting wrong answer with Match Function

Excel 2007, HP PC. I have a long array of data, with a varying number of
entries per each individual on whom training data is kept, based on what
events they have not completed. I use a simple formula in a column to the
left of the array (column A) to put a sequential number by the first entry
for each individual. I am using the Match function (within an Address
function) to search column A to find the first row of a person's individual
entries. The error I am encountering is that the Match function occasionally
returns the wrong row number! For example, if the first entry for the 6th
individual is actually on row 190, I've gotten 180 as the answer when I step
through the formula audit. Any thoughts?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 793
Default Getting wrong answer with Match Function

Need the formula and sample data before making any comment...

"CB1215" wrote:

Excel 2007, HP PC. I have a long array of data, with a varying number of
entries per each individual on whom training data is kept, based on what
events they have not completed. I use a simple formula in a column to the
left of the array (column A) to put a sequential number by the first entry
for each individual. I am using the Match function (within an Address
function) to search column A to find the first row of a person's individual
entries. The error I am encountering is that the Match function occasionally
returns the wrong row number! For example, if the first entry for the 6th
individual is actually on row 190, I've gotten 180 as the answer when I step
through the formula audit. Any thoughts?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Getting wrong answer with Match Function

Post your formula.

--
Biff
Microsoft Excel MVP


"CB1215" wrote in message
...
Excel 2007, HP PC. I have a long array of data, with a varying number of
entries per each individual on whom training data is kept, based on what
events they have not completed. I use a simple formula in a column to the
left of the array (column A) to put a sequential number by the first
entry
for each individual. I am using the Match function (within an Address
function) to search column A to find the first row of a person's
individual
entries. The error I am encountering is that the Match function
occasionally
returns the wrong row number! For example, if the first entry for the 6th
individual is actually on row 190, I've gotten 180 as the answer when I
step
through the formula audit. Any thoughts?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Getting wrong answer with Match Function

Make sure you tell excel that you're looking for an exact match.

=match(a1,sheet2!a:a,0)
(that 0 (or False) forces an exact match)

And if the lookup value contains a wildcard (?, *, or ~), then that could cause
trouble.

=match(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~ "),"?","~?"),"*","~*"),
Sheet2!a:a,0)



CB1215 wrote:

Excel 2007, HP PC. I have a long array of data, with a varying number of
entries per each individual on whom training data is kept, based on what
events they have not completed. I use a simple formula in a column to the
left of the array (column A) to put a sequential number by the first entry
for each individual. I am using the Match function (within an Address
function) to search column A to find the first row of a person's individual
entries. The error I am encountering is that the Match function occasionally
returns the wrong row number! For example, if the first entry for the 6th
individual is actually on row 190, I've gotten 180 as the answer when I step
through the formula audit. Any thoughts?


--

Dave Peterson
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
Cosine Function Returns Wrong Answer! Gordon Arnaut Excel Discussion (Misc queries) 16 April 3rd 23 02:24 PM
Calculator Answer Doesn't Match Excel Answer GwenH Excel Discussion (Misc queries) 3 October 20th 08 10:17 AM
Using Indirect in a Sumif Function returns the wrong answer Grahin Excel Discussion (Misc queries) 4 December 21st 07 03:11 PM
The wrong answer Adam Excel Discussion (Misc queries) 1 October 29th 07 12:22 PM
Using MATCH function does not return correct answer LindaBabe54 Excel Worksheet Functions 2 July 31st 06 02:53 PM


All times are GMT +1. The time now is 10:06 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"