Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

[richtks]

Trying to return a blank instead of a #N/A or 0 with INDEX and MATCH. Formula Below...

=INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0))


Thanks in advance for your assistance and time.

Regards

Richard T.

 

[Fluff]

Hi & welcome to MrExcel.
How about
=IFERROR(INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0)),"")

 

[ClaireS]

If the formula only returns the #NA error value then =IFERROR(

INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0)),"") returns the value, or a null text string "" if the result is an error.
Otherwise you'll need an IF function as well:
=IFERROR(IF(

INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0))=0,"",

INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0))),"")​

​

_{^{<strike>
</strike>}}
​

 

[richtks]

Fluff's suggestion removed the #N/A's but still had the 0.

ClaireS, your suggestion HOOKED IT UP!!!

Thanks!!!

 

[richtks]

What if I just have 0's

=INDEX(Data_InFlight[Hard Cost Vendor],MATCH([Product Key],Data_InFlight[Product Key],0))

 

[Fluff]

Can you please explain?

 

[richtks]

Claires removed the 0's and the #N/A's. your first suggestion removed the #N/A's. I have some columns that are just returning 0's.

 

[ClaireS]

Thanks, glad I was able to help.

 

[Fluff]

In that case use ClaireS' formula

 

[richtks]

It worked. I must have done something wrong at first, cause it was removing some of the info.... Thanks Guys!!!