Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cosine Function Returns Wrong Answer! | Excel Discussion (Misc queries) | |||
Calculator Answer Doesn't Match Excel Answer | Excel Discussion (Misc queries) | |||
Using Indirect in a Sumif Function returns the wrong answer | Excel Discussion (Misc queries) | |||
The wrong answer | Excel Discussion (Misc queries) | |||
Using MATCH function does not return correct answer | Excel Worksheet Functions |