Match function result in #N/A

ezfan

New Member
Joined
Nov 29, 2011
Messages
8
Dear Exceler,

I have a problem that appreciate if anyone can help?

I have tried doing a function to extract unique list and remove duplicates. It results in #N/A.

=INDEX(tt,MATCH(0,COUNTIF(tt,"<"&tt)-SUM(COUNTIF(tt,C$1:C1)),0)) where tt is:
=$A$4:INDEX($A$2:$A$20,MATCH($A$1,$B$2:$B$20)) -->

I check the error in excel for the first formula and tried pressing F9 for each of the function and they all work.
For the first formula, when I only leave the COUNTIF and SUM function in the cell, it showed value.
When I leave the whole MATCH function, it leaves a #N/A. However, when I checked in the edit formula bar by pressing F9, it result in the value as expected.
I dont understand why it would results in #N/A in excel but F9 showed it has no problem.

Anyone can help? Thanks.

Regards,
ezfan
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Dear Exceler,

I have a problem that appreciate if anyone can help?

I have tried doing a function to extract unique list and remove duplicates. It results in #N/A.

=INDEX(tt,MATCH(0,COUNTIF(tt,"<"&tt)-SUM(COUNTIF(tt,C$1:C1)),0)) where tt is:
=$A$4:INDEX($A$2:$A$20,MATCH($A$1,$B$2:$B$20,0)) -->

I check the error in excel for the first formula and tried pressing F9 for each of the function and they all work.
For the first formula, when I only leave the COUNTIF and SUM function in the cell, it showed value.
When I leave the whole MATCH function, it leaves a #N/A. However, when I checked in the edit formula bar by pressing F9, it result in the value as expected.
I dont understand why it would results in #N/A in excel but F9 showed it has no problem.

Anyone can help? Thanks.

Regards,
ezfan
Does it make a difference if you use a match_type of 0 in your range expression:

=$A$4:INDEX($A$2:$A$20,MATCH($A$1,$B$2:$B$20,0))

If that doesn't work then post some sample data and tell us what result you expect.
 

ezfan

New Member
Joined
Nov 29, 2011
Messages
8
Dear Biff,

Thanks for the reply.

I have tried the change and no change in the result.
Here's the screenshot of the data:
Col A ColB ColC
Product Code Count Product
SM301 1 #N/A
SM302 2 #N/A
SM303 3 #N/A
SM304 4 #N/A
SM305 5 #N/A
#N/A
#N/A

Please note that my Col A actually are formula that pull data from another sheet and hence the blank below the SM305 are actually formula. Therefore, did not use exact formula from other Magic Trick to search for REPT('z") but used COlB to find the last row and I defined tt for the colA range.

Please help.

Best regards,
ezfan
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Dear Biff,

Thanks for the reply.

I have tried the change and no change in the result.
Here's the screenshot of the data:
Col A ColB ColC
Product Code Count Product
SM301 1 #N/A
SM302 2 #N/A
SM303 3 #N/A
SM304 4 #N/A
SM305 5 #N/A
#N/A
#N/A

Please note that my Col A actually are formula that pull data from another sheet and hence the blank below the SM305 are actually formula. Therefore, did not use exact formula from other Magic Trick to search for REPT('z") but used COlB to find the last row and I defined tt for the colA range.

Please help.

Best regards,
ezfan
Ok, I'm not sure what you're trying to do with this?

Do you want to extract the unique products?
 

ezfan

New Member
Joined
Nov 29, 2011
Messages
8

ADVERTISEMENT

Dear Biff,

I actually tried following magic trick worksheet formula and doing the same in y Excel 2007 version (also tried the same in office 2010 version. However, in both cases, when the formula is used in my workbook, they in F9 can return with a value, however, excel will at the end result in N/A.

I will try again put my scenario as below: My objective is to return the 3 products as unique value and only have 3 enties to the product column.

Sheet:
Product Code Product
TIN1205601 #N/A
TIN1205601 #N/A
TIN1205601 #N/A
TIN1205602 #N/A
TIN1205602 #N/A
TIN1205602 #N/A
TIN1205602 #N/A
TIN1205603 #N/A
TIN1205603 #N/A
TIN1205603 #N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

The formula in the #N/A column now using the formula:
=INDEX(tt,MATCH(0,COUNTIF(tt,"<"&tt)-SUM(COUNTIF(tt,C$1:C1)),0))

tt now = =Sheet2!$A$2:INDEX(Sheet2!$A$2:$A$18,MATCH(REPT("z",255),Sheet2!$A$2:$A$18))

Will there be case that some formula only works in a particular workbook?

Thanks.
Regards,
ezfan
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Dear Biff,

I actually tried following magic trick worksheet formula and doing the same in y Excel 2007 version (also tried the same in office 2010 version. However, in both cases, when the formula is used in my workbook, they in F9 can return with a value, however, excel will at the end result in N/A.

I will try again put my scenario as below: My objective is to return the 3 products as unique value and only have 3 enties to the product column.

Sheet:
Product Code Product
TIN1205601 #N/A
TIN1205601 #N/A
TIN1205601 #N/A
TIN1205602 #N/A
TIN1205602 #N/A
TIN1205602 #N/A
TIN1205602 #N/A
TIN1205603 #N/A
TIN1205603 #N/A
TIN1205603 #N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

The formula in the #N/A column now using the formula:
=INDEX(tt,MATCH(0,COUNTIF(tt,"<"&tt)-SUM(COUNTIF(tt,C$1:C1)),0))

tt now = =Sheet2!$A$2:INDEX(Sheet2!$A$2:$A$18,MATCH(REPT("z",255),Sheet2!$A$2:$A$18))

Will there be case that some formula only works in a particular workbook?

Thanks.
Regards,
ezfan
Ok, try this...

I put everything on one sheet to make the exhibit easy to see/understand.

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:86px;" /><col style="width:21px;" /><col style="width:87px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">TIN1205601</td><td style="color:#ffffff; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TIN1205601</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205601</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TIN1205602</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205601</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TIN1205603</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205602</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205602</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205602</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205602</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205603</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205603</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">TIN1205603</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></table> <br /><br />
Redefine the named range tt as:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$18,MATCH("zzzzz",Sheet1!$A$2:$A$18))

Enter this formula in C2:

=A2

Enter this array formula** in C3 and copy down until you get blanks:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(tt,MATCH(0,COUNTIF(C$2:C2,tt),0))))

If you're using Excel 2007 or later then we can reduce that array formula** to:

=IFERROR(INDEX(tt,MATCH(0,COUNTIF(C$2:C2,tt),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 

ezfan

New Member
Joined
Nov 29, 2011
Messages
8
Hi Biff,

Thanks a lot for your help.

It works now! Great Help! Really appreciated!\

Regards,
ezfan
 

Watch MrExcel Video

Forum statistics

Threads
1,122,802
Messages
5,598,122
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top