Match function result in #N/A

[ezfan]

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

 

[T. Valko]

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]

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]

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]

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]

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.

Book1
	A	B	C
2	TIN1205601	_	TIN1205601
3	TIN1205601	_	TIN1205602
4	TIN1205601	_	TIN1205603
5	TIN1205602	_	_
6	TIN1205602	_	_
7	TIN1205602	_	_
8	TIN1205602	_	_
9	TIN1205603	_	_
10	TIN1205603	_	_
11	TIN1205603	_	_
Sheet1

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]

Hi Biff,

Thanks a lot for your help.

It works now! Great Help! Really appreciated!\

Regards,
ezfan

 

[T. Valko]

Hi Biff,

Thanks a lot for your help.

It works now! Great Help! Really appreciated!\

Regards,
ezfan

You're welcome. Thanks for the feedback!