• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match formula that returns #N/A error

M

mugshaw

New Member
Hi there

I have made a formula to extract the unique product values from a table, however I am getting an #N/A error which I do not understand.

Basically the formula works in the first cell, however when I drag it down I receive an #N/A error. I believe it is because of the range $A1$:A1 in the formula but I have tried quite a few things and the error message remains.

The formula looks like this: {=INDEX(Table1[ProductNumbers],MATCH(1,IF((Table1[ProductNumbers]<>$A1$:A1)*(Table1[Year]="2016"),1,0),0))}

I would appreciate if someone could tell me why I receive the error and how it can be fixed.
 

Attachments

try below with ctl +shift +enter

=IFERROR(INDEX(Table1[ProductNumbers],MATCH(0,COUNTIF($G$1:G1,Table1[ProductNumbers])*COUNTIF(Table1[Year],2016),0)),"")


mugshaw said:
Hi there

I have made a formula to extract the unique product values from a table, however I am getting an #N/A error which I do not understand.

Basically the formula works in the first cell, however when I drag it down I receive an #N/A error. I believe it is because of the range $A1$:A1 in the formula but I have tried quite a few things and the error message remains.

The formula looks like this: {=INDEX(Table1[ProductNumbers],MATCH(1,IF((Table1[ProductNumbers]<>$A1$:A1)*(Table1[Year]="2016"),1,0),0))}

I would appreciate if someone could tell me why I receive the error and how it can be fixed.
Click to expand...
mugshaw said:
Hi there

I have made a formula to extract the unique product values from a table, however I am getting an #N/A error which I do not understand.

Basically the formula works in the first cell, however when I drag it down I receive an #N/A error. I believe it is because of the range $A1$:A1 in the formula but I have tried quite a few things and the error message remains.

The formula looks like this: {=INDEX(Table1[ProductNumbers],MATCH(1,IF((Table1[ProductNumbers]<>$A1$:A1)*(Table1[Year]="2016"),1,0),0))}

I would appreciate if someone could tell me why I receive the error and how it can be fixed.
Click to expand...
 
Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=IFERROR(INDEX(Table1[ProductNumbers],MATCH(0,("2016"<>Table1[Year])+(COUNTIF($G$1:G1,Table1[ProductNumbers])),0)),"")

Enter the formula in G2 and copy down.

Narayan
 
NARAYANK991 said:
Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=IFERROR(INDEX(Table1[ProductNumbers],MATCH(0,("2016"<>Table1[Year])+(COUNTIF($G$1:G1,Table1[ProductNumbers])),0)),"")

Enter the formula in G2 and copy down.

Narayan
Click to expand...

Thanks so much... it does exactly whats its supposed to do :)
 
You must log in or register to reply here.
Back
Top