How to make INDEX and MATCH formula return blank instead of 0

[Sean15]

Hi:
The formula =INDEX(WhoDoesIt,MATCH(A2,MainAccount,0)) returns 0 when there is a blank cell in the INDEX range. How can I adapt formula so Excel return blank instead of 0
Thanks for your help.

Regards
Sean

 

[Fluff]

Are the returned values text, or numbers?

 

[jamiemarie]

I use =IFERROR(INDEX(WhoDoesIt,MATCH(A2,MainAccount,0)),"")

 

[Sean15]

Hi:
Returned values are text but there are several cells in the INDEX range that are blank.

Regards

 

[CA_Punit]

First Select all the Index Range and Ctrl+Find
Find
Replace With '.
In this way Blank Cell will be converted into Text "" and it will not result in "Zero".

This is a option
Or can use If (Index Formula=0,"",Indexformula)

 

[Fluff]

In that case try
=Iferror(T(INDEX(WhoDoesIt,MATCH(A2,MainAccount,0))),"")

 

[Fluff]

First Select all the Index Range and Ctrl+Find
Find
Replace With '.
In this way Blank Cell will be converted into Text "" and it will not result in "Zero".

IMO this is a very bad idea, because the cells are no longer blank & that could cause all sorts of problems.

 

[Sean15]

In that case try
=Iferror(T(INDEX(WhoDoesIt,MATCH(A2,MainAccount,0))),"")

That works. Thanks so much.
What is the purpose of the T in your formula?

Regards,

 

[Fluff]

It checks if a value is text, if it is, it returns the text otherwise it returns a null string "", which will then return an error.

 

[CA_Punit]

So if there are Number and Text both in the returned value you can use Index(..........)&""
It will convert Number into Text,. Blank into null string, and Text will remain as Text.

However I have a query is there any way through which you can convert the Number which is converted to text because of using &"" back into Number. And also not returning value error when text is returned while using Index function.

@Fluff