Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
I have a spreadsheet with 2 lists of data, each list contains a number of
columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and list 2 is about 50,000 rows The left most column in each list contains part numbers, these are unique values that sometimes contain alpha characters and sometimes not. The first list is a 'finished' list that I wish to use for display, but the second list has a column that contains a statistic that I have been asked to incorporate into the finished list. The second list also has most of the part numbers present in the first list (about 94%), so I didn't think the task would be too hard, a relatively simple formula should do the trick - but my formula has returned results that I do not understand. My formula is thus: "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1),4,1,1),0)" Now the formula works fine with the exception of the MATCH function, the cell A2636 has the part number 1672312 with no spaces before or after. The part number 1672312 exists in the second list in cell Q1557, again with no spaces before or after and as can be seen by the formula, the value I want is in the fifth column of the list. The MATCH function seems to want to match to a part number 167-2222 which is located in the cell Q7638. I tried altering the format of the part number columns to text, but it made no difference. I also tried altering the MATCH_TYPE argument of the function from 1 to 0 to force an exact match, the formula then returns an error. Now I do not consider myself an Excel novice and I know that this task would be easily accomplished with a little VBA, but that's not the point. I have read the help file again to make sure that I was using the MATCH function correctly. I tried alternatives like VLOOKUP to see what it matched to, again it matches to the same incorrect cell. I suspect formatting of the part number columns somehow, but I don't know how - the part number columns contain absolute values, no underlying formula's, no linked cells or anything like that. Using the Formula Evaluate tool shows me that the cell A2636 evaluates to the correct part number, but from there is goes south. Anyone have any ideas? -- Regards - Peter |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
What error do you get when you change the Match Type argument?
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Peter" wrote in message ... I have a spreadsheet with 2 lists of data, each list contains a number of columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and list 2 is about 50,000 rows The left most column in each list contains part numbers, these are unique values that sometimes contain alpha characters and sometimes not. The first list is a 'finished' list that I wish to use for display, but the second list has a column that contains a statistic that I have been asked to incorporate into the finished list. The second list also has most of the part numbers present in the first list (about 94%), so I didn't think the task would be too hard, a relatively simple formula should do the trick - but my formula has returned results that I do not understand. My formula is thus: "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1),4,1,1),0)" Now the formula works fine with the exception of the MATCH function, the cell A2636 has the part number 1672312 with no spaces before or after. The part number 1672312 exists in the second list in cell Q1557, again with no spaces before or after and as can be seen by the formula, the value I want is in the fifth column of the list. The MATCH function seems to want to match to a part number 167-2222 which is located in the cell Q7638. I tried altering the format of the part number columns to text, but it made no difference. I also tried altering the MATCH_TYPE argument of the function from 1 to 0 to force an exact match, the formula then returns an error. Now I do not consider myself an Excel novice and I know that this task would be easily accomplished with a little VBA, but that's not the point. I have read the help file again to make sure that I was using the MATCH function correctly. I tried alternatives like VLOOKUP to see what it matched to, again it matches to the same incorrect cell. I suspect formatting of the part number columns somehow, but I don't know how - the part number columns contain absolute values, no underlying formula's, no linked cells or anything like that. Using the Formula Evaluate tool shows me that the cell A2636 evaluates to the correct part number, but from there is goes south. Anyone have any ideas? -- Regards - Peter |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
You will need to have the match-type parameter set to 0 to look for an
exact match - what error does this return? Pete On Oct 30, 9:45 pm, Peter wrote: I have a spreadsheet with 2 lists of data, each list contains a number of columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and list 2 is about 50,000 rows The left most column in each list contains part numbers, these are unique values that sometimes contain alpha characters and sometimes not. The first list is a 'finished' list that I wish to use for display, but the second list has a column that contains a statistic that I have been asked to incorporate into the finished list. The second list also has most of the part numbers present in the first list (about 94%), so I didn't think the task would be too hard, a relatively simple formula should do the trick - but my formula has returned results that I do not understand. My formula is thus: "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1*),4,1,1),0)" Now the formula works fine with the exception of the MATCH function, the cell A2636 has the part number 1672312 with no spaces before or after. The part number 1672312 exists in the second list in cell Q1557, again with no spaces before or after and as can be seen by the formula, the value I want is in the fifth column of the list. The MATCH function seems to want to match to a part number 167-2222 which is located in the cell Q7638. I tried altering the format of the part number columns to text, but it made no difference. I also tried altering the MATCH_TYPE argument of the function from 1 to 0 to force an exact match, the formula then returns an error. Now I do not consider myself an Excel novice and I know that this task would be easily accomplished with a little VBA, but that's not the point. I have read the help file again to make sure that I was using the MATCH function correctly. I tried alternatives like VLOOKUP to see what it matched to, again it matches to the same incorrect cell. I suspect formatting of the part number columns somehow, but I don't know how - the part number columns contain absolute values, no underlying formula's, no linked cells or anything like that. Using the Formula Evaluate tool shows me that the cell A2636 evaluates to the correct part number, but from there is goes south. Anyone have any ideas? -- Regards - Peter |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
The error that is returned is "#N/A"
-- Regards - Peter "Pete_UK" wrote: You will need to have the match-type parameter set to 0 to look for an exact match - what error does this return? Pete On Oct 30, 9:45 pm, Peter wrote: I have a spreadsheet with 2 lists of data, each list contains a number of columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and list 2 is about 50,000 rows The left most column in each list contains part numbers, these are unique values that sometimes contain alpha characters and sometimes not. The first list is a 'finished' list that I wish to use for display, but the second list has a column that contains a statistic that I have been asked to incorporate into the finished list. The second list also has most of the part numbers present in the first list (about 94%), so I didn't think the task would be too hard, a relatively simple formula should do the trick - but my formula has returned results that I do not understand. My formula is thus: "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1-),4,1,1),0)" Now the formula works fine with the exception of the MATCH function, the cell A2636 has the part number 1672312 with no spaces before or after. The part number 1672312 exists in the second list in cell Q1557, again with no spaces before or after and as can be seen by the formula, the value I want is in the fifth column of the list. The MATCH function seems to want to match to a part number 167-2222 which is located in the cell Q7638. I tried altering the format of the part number columns to text, but it made no difference. I also tried altering the MATCH_TYPE argument of the function from 1 to 0 to force an exact match, the formula then returns an error. Now I do not consider myself an Excel novice and I know that this task would be easily accomplished with a little VBA, but that's not the point. I have read the help file again to make sure that I was using the MATCH function correctly. I tried alternatives like VLOOKUP to see what it matched to, again it matches to the same incorrect cell. I suspect formatting of the part number columns somehow, but I don't know how - the part number columns contain absolute values, no underlying formula's, no linked cells or anything like that. Using the Formula Evaluate tool shows me that the cell A2636 evaluates to the correct part number, but from there is goes south. Anyone have any ideas? -- Regards - Peter |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
Peter wrote...
.... The left most column in each list contains part numbers, these are unique values that sometimes contain alpha characters and sometimes not. The first list is a 'finished' list that I wish to use for display, but the second list has a column that contains a statistic that I have been asked to incorporate into the finished list. The second list also has most of the part numbers present in the first list (about 94%), so I didn't think the task would be too hard, a relatively simple formula should do the trick - but my formula has returned results that I do not understand. .... =IF(COUNTIF($Q$2:$Q49959,A2636)<0, OFFSET($Q$1,MATCH(A2636,$Q$2:$Q$49959,1),4,1,1),0 ) Your MATCH call, using 3rd argument 1, assumes Q2:Q49959 is sorted in what Excel considers ascending order, meaning numbers first in ascending order, then mixed numerals and letters in text order. Unless you've sorted your second table on its first column, I'd bet the problem is that this column isn't sorted as Excel needs it. Also, if A2636 contained the NUMBER 123456, that would NOT match the text "123456" in Q2:Q49959 and vice versa. You should ensure that all cells in Q2:Q49959 *AND* cell A2636 (or, maybe, all cells in the first column of your first table) are text, and then sort the second table into ascending order on its first column. Now the formula works fine with the exception of the MATCH function, the cell A2636 has the part number 1672312 with no spaces before or after. The part number 1672312 exists in the second list in cell Q1557, again with no spaces before or after and as can be seen by the formula, the value I want is in the fifth column of the list. If the formula =ISNUMBER(A2636)=ISNUMBER(Q1557) returns FALSE then one of these cells is numeric, the other text, and Excel will *NOT* consider them equal. The MATCH function seems to want to match to a part number 167-2222 which is located in the cell Q7638. This would tend to imply that Q1557 is numeric and A2636 is text. I tried altering the format of the part number columns to text, but it made no difference. Of course not. Number formatting doesn't, never has and never will affect the value a cell contains. If you have a numeric value in a cell, the ONLY way to change it to text is to ENTER text. That can be achieved by changing the number format to Text then re-entering the cell, and the re-entry is NECESSARY. I also tried altering the MATCH_TYPE argument of the function from 1 to 0 to force an exact match, the formula then returns an error. .... As it should. Exact matching also REQUIRES that value sought (first argument) and value found in the lookup range (second argument) are the same type, either both text or both numbers. Excel will NEVER match number against text or vice versa. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
If you try a simple comparison of the two cells that you believe are
identical and should return a positive result, what do you get? eg Assuming you had two values in say A1 and F3, then a simple =A1=F3 would either return TRUE or FALSE. What does thsi give on your two values? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Peter" wrote in message ... The error that is returned is "#N/A" -- Regards - Peter "Pete_UK" wrote: You will need to have the match-type parameter set to 0 to look for an exact match - what error does this return? Pete On Oct 30, 9:45 pm, Peter wrote: I have a spreadsheet with 2 lists of data, each list contains a number of columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and list 2 is about 50,000 rows The left most column in each list contains part numbers, these are unique values that sometimes contain alpha characters and sometimes not. The first list is a 'finished' list that I wish to use for display, but the second list has a column that contains a statistic that I have been asked to incorporate into the finished list. The second list also has most of the part numbers present in the first list (about 94%), so I didn't think the task would be too hard, a relatively simple formula should do the trick - but my formula has returned results that I do not understand. My formula is thus: "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1-),4,1,1),0)" Now the formula works fine with the exception of the MATCH function, the cell A2636 has the part number 1672312 with no spaces before or after. The part number 1672312 exists in the second list in cell Q1557, again with no spaces before or after and as can be seen by the formula, the value I want is in the fifth column of the list. The MATCH function seems to want to match to a part number 167-2222 which is located in the cell Q7638. I tried altering the format of the part number columns to text, but it made no difference. I also tried altering the MATCH_TYPE argument of the function from 1 to 0 to force an exact match, the formula then returns an error. Now I do not consider myself an Excel novice and I know that this task would be easily accomplished with a little VBA, but that's not the point. I have read the help file again to make sure that I was using the MATCH function correctly. I tried alternatives like VLOOKUP to see what it matched to, again it matches to the same incorrect cell. I suspect formatting of the part number columns somehow, but I don't know how - the part number columns contain absolute values, no underlying formula's, no linked cells or anything like that. Using the Formula Evaluate tool shows me that the cell A2636 evaluates to the correct part number, but from there is goes south. Anyone have any ideas? -- Regards - Peter |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
This means that there is no exact match. Are you sure that the
formatting is the same for the lookup-value and the lookup-range? Could one be a number and the other be text? You say that there are leading or trailing spaces - have you applied TRIM to get rid of any that might be lurking? Could you possibly have any non-breaking space characters (char 160)? Basically, you need to check the data thoroughly, and the data types must be the same. Hope this helps. Pete On Oct 30, 10:15 pm, Peter wrote: The error that is returned is "#N/A" -- Regards - Peter "Pete_UK" wrote: You will need to have the match-type parameter set to 0 to look for an exact match - what error does this return? Pete On Oct 30, 9:45 pm, Peter wrote: I have a spreadsheet with 2 lists of data, each list contains a number of columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and list 2 is about 50,000 rows The left most column in each list contains part numbers, these are unique values that sometimes contain alpha characters and sometimes not. The first list is a 'finished' list that I wish to use for display, but the second list has a column that contains a statistic that I have been asked to incorporate into the finished list. The second list also has most of the part numbers present in the first list (about 94%), so I didn't think the task would be too hard, a relatively simple formula should do the trick - but my formula has returned results that I do not understand. My formula is thus: "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1*-),4,1,1),0)" Now the formula works fine with the exception of the MATCH function, the cell A2636 has the part number 1672312 with no spaces before or after. The part number 1672312 exists in the second list in cell Q1557, again with no spaces before or after and as can be seen by the formula, the value I want is in the fifth column of the list. The MATCH function seems to want to match to a part number 167-2222 which is located in the cell Q7638. I tried altering the format of the part number columns to text, but it made no difference. I also tried altering the MATCH_TYPE argument of the function from 1 to 0 to force an exact match, the formula then returns an error. Now I do not consider myself an Excel novice and I know that this task would be easily accomplished with a little VBA, but that's not the point. I have read the help file again to make sure that I was using the MATCH function correctly. I tried alternatives like VLOOKUP to see what it matched to, again it matches to the same incorrect cell. I suspect formatting of the part number columns somehow, but I don't know how - the part number columns contain absolute values, no underlying formula's, no linked cells or anything like that. Using the Formula Evaluate tool shows me that the cell A2636 evaluates to the correct part number, but from there is goes south. Anyone have any ideas? -- Regards - Peter- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
<part number 1672312 with no spaces before or after
It may have no spaces before or after, but there may still be differences between text and number. Test that with the ISNUMBER() or ISTEXT() functions, and compare the length of the items with the LENGTH() function for the presence of invisible characters (like non-breaking spaces, which often occur in files downloaded from the Internet) -- Kind regards, Niek Otten Microsoft MVP - Excel "Peter" wrote in message ... |I have a spreadsheet with 2 lists of data, each list contains a number of | columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and | list 2 is about 50,000 rows | | The left most column in each list contains part numbers, these are unique | values that sometimes contain alpha characters and sometimes not. | | The first list is a 'finished' list that I wish to use for display, but the | second list has a column that contains a statistic that I have been asked to | incorporate into the finished list. | | The second list also has most of the part numbers present in the first list | (about 94%), so I didn't think the task would be too hard, a relatively | simple formula should do the trick - but my formula has returned results that | I do not understand. | | My formula is thus: | | "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1),4,1,1),0)" | | Now the formula works fine with the exception of the MATCH function, the | cell A2636 has the part number 1672312 with no spaces before or after. | | The part number 1672312 exists in the second list in cell Q1557, again with | no spaces before or after and as can be seen by the formula, the value I want | is in the fifth column of the list. | | The MATCH function seems to want to match to a part number 167-2222 which is | located in the cell Q7638. | | I tried altering the format of the part number columns to text, but it made | no difference. I also tried altering the MATCH_TYPE argument of the function | from 1 to 0 to force an exact match, the formula then returns an error. | | Now I do not consider myself an Excel novice and I know that this task would | be easily accomplished with a little VBA, but that's not the point. I have | read the help file again to make sure that I was using the MATCH function | correctly. I tried alternatives like VLOOKUP to see what it matched to, again | it matches to the same incorrect cell. | | I suspect formatting of the part number columns somehow, but I don't know | how - the part number columns contain absolute values, no underlying | formula's, no linked cells or anything like that. Using the Formula Evaluate | tool shows me that the cell A2636 evaluates to the correct part number, but | from there is goes south. | | Anyone have any ideas? | -- | Regards - Peter | |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
After a little more research I have discovered that the part numbers that
contain mixed alpha and numeric values match exactly, the only ones that fail are the those that are all numeric. I was apparently supposed to have had this out yesterday - oops, so I have written a sub that has done the trick In response to your question Ken, the comparison produces a TRUE result. -- Regards - Peter "Ken Wright" wrote: If you try a simple comparison of the two cells that you believe are identical and should return a positive result, what do you get? eg Assuming you had two values in say A1 and F3, then a simple =A1=F3 would either return TRUE or FALSE. What does thsi give on your two values? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "Peter" wrote in message ... The error that is returned is "#N/A" -- Regards - Peter "Pete_UK" wrote: You will need to have the match-type parameter set to 0 to look for an exact match - what error does this return? Pete On Oct 30, 9:45 pm, Peter wrote: I have a spreadsheet with 2 lists of data, each list contains a number of columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and list 2 is about 50,000 rows The left most column in each list contains part numbers, these are unique values that sometimes contain alpha characters and sometimes not. The first list is a 'finished' list that I wish to use for display, but the second list has a column that contains a statistic that I have been asked to incorporate into the finished list. The second list also has most of the part numbers present in the first list (about 94%), so I didn't think the task would be too hard, a relatively simple formula should do the trick - but my formula has returned results that I do not understand. My formula is thus: "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1-),4,1,1),0)" Now the formula works fine with the exception of the MATCH function, the cell A2636 has the part number 1672312 with no spaces before or after. The part number 1672312 exists in the second list in cell Q1557, again with no spaces before or after and as can be seen by the formula, the value I want is in the fifth column of the list. The MATCH function seems to want to match to a part number 167-2222 which is located in the cell Q7638. I tried altering the format of the part number columns to text, but it made no difference. I also tried altering the MATCH_TYPE argument of the function from 1 to 0 to force an exact match, the formula then returns an error. Now I do not consider myself an Excel novice and I know that this task would be easily accomplished with a little VBA, but that's not the point. I have read the help file again to make sure that I was using the MATCH function correctly. I tried alternatives like VLOOKUP to see what it matched to, again it matches to the same incorrect cell. I suspect formatting of the part number columns somehow, but I don't know how - the part number columns contain absolute values, no underlying formula's, no linked cells or anything like that. Using the Formula Evaluate tool shows me that the cell A2636 evaluates to the correct part number, but from there is goes south. Anyone have any ideas? -- Regards - Peter |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
Okay, that's interesting - I've never struck non-breaking spaces before.
Definately something to look out for, thanks. The source for this file was a flat file export from a database, it basically produces a text output that you can open with Excel and do what you like with. -- Regards - Peter "Niek Otten" wrote: <part number 1672312 with no spaces before or after It may have no spaces before or after, but there may still be differences between text and number. Test that with the ISNUMBER() or ISTEXT() functions, and compare the length of the items with the LENGTH() function for the presence of invisible characters (like non-breaking spaces, which often occur in files downloaded from the Internet) -- Kind regards, Niek Otten Microsoft MVP - Excel "Peter" wrote in message ... |I have a spreadsheet with 2 lists of data, each list contains a number of | columns, the 2 lists are not equal in length, list 1 is about 2,600 rows and | list 2 is about 50,000 rows | | The left most column in each list contains part numbers, these are unique | values that sometimes contain alpha characters and sometimes not. | | The first list is a 'finished' list that I wish to use for display, but the | second list has a column that contains a statistic that I have been asked to | incorporate into the finished list. | | The second list also has most of the part numbers present in the first list | (about 94%), so I didn't think the task would be too hard, a relatively | simple formula should do the trick - but my formula has returned results that | I do not understand. | | My formula is thus: | | "=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1),4,1,1),0)" | | Now the formula works fine with the exception of the MATCH function, the | cell A2636 has the part number 1672312 with no spaces before or after. | | The part number 1672312 exists in the second list in cell Q1557, again with | no spaces before or after and as can be seen by the formula, the value I want | is in the fifth column of the list. | | The MATCH function seems to want to match to a part number 167-2222 which is | located in the cell Q7638. | | I tried altering the format of the part number columns to text, but it made | no difference. I also tried altering the MATCH_TYPE argument of the function | from 1 to 0 to force an exact match, the formula then returns an error. | | Now I do not consider myself an Excel novice and I know that this task would | be easily accomplished with a little VBA, but that's not the point. I have | read the help file again to make sure that I was using the MATCH function | correctly. I tried alternatives like VLOOKUP to see what it matched to, again | it matches to the same incorrect cell. | | I suspect formatting of the part number columns somehow, but I don't know | how - the part number columns contain absolute values, no underlying | formula's, no linked cells or anything like that. Using the Formula Evaluate | tool shows me that the cell A2636 evaluates to the correct part number, but | from there is goes south. | | Anyone have any ideas? | -- | Regards - Peter | |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH function not matching as expected
I would like to thank everyone who took the time to offer help with my
problem, I appreciate it. -- Regards - Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Average function not returning expected result | Excel Worksheet Functions | |||
Average function not returning expected result | Excel Discussion (Misc queries) | |||
LOOKUP function not returning expected value - Using vector_lookup format | Excel Worksheet Functions | |||
Hyperlink Function not working as expected | Excel Discussion (Misc queries) | |||
compile error: expected variable or function | Excel Discussion (Misc queries) |