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

richtks

New Member
Joined
Jun 1, 2019
Messages
7
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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
=IFERROR(INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0)),"")
 

ClaireS

Board Regular
Joined
Jul 29, 2013
Messages
136
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

New Member
Joined
Jun 1, 2019
Messages
7
Fluff's suggestion removed the #N/A's but still had the 0.

ClaireS, your suggestion HOOKED IT UP!!!

Thanks!!!
 

richtks

New Member
Joined
Jun 1, 2019
Messages
7
What if I just have 0's

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
Can you please explain?
 

richtks

New Member
Joined
Jun 1, 2019
Messages
7
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
In that case use ClaireS' formula
 

richtks

New Member
Joined
Jun 1, 2019
Messages
7
It worked. I must have done something wrong at first, cause it was removing some of the info.... Thanks Guys!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,089,862
Messages
5,410,856
Members
403,331
Latest member
dignityy

This Week's Hot Topics

Top