Why is my MATCH inserting a 0 if no match is found?

[jmpatrick]

Good morning!

Here's my function:

=IFERROR(INDEX(Subdivisions!Z:Z,MATCH(AI872,Subdivisions!C:C,0)),"")

If no MATCH is found the result is a 0. I need it to be blank. What am I doing wrong?

jp

 

[etaf]

if no match is found - or if there is a match but the value in Z:Z is blank , then it will return a zero
the iferror will kick in if the value in AI872 is NOT found

so will Z:Z have blanks corresponding to lookup value in C:C
and do you want to return a blank
are there zeros in the data ?

this may help

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

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

www.mrexcel.com

 

[jmpatrick]

if no match is found - or if there is a match but the value in Z:Z is blank , then it will return a zero
the iferror will kick in if the value in AI872 is NOT found

so will Z:Z have blanks corresponding to lookup value in C:C
and do you want to return a blank
are there zeros in the data ?

this may help

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

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

www.mrexcel.com

The information from the link worked great. I changed the function to this:

=IFERROR(T(INDEX(Subdivisions!Z:Z,MATCH(AI872,Subdivisions!C:C,0))),"")

Thanks!

 

[etaf]

you are welcome

 

[etaf]

you can use an ISBLANK() if you have a mixture of text and numbers or just all numbers returned , I tried the N function but still returned a zero for a blank
maybe a better way , but thats all i can find

=IFERROR(IF(ISBLANK(INDEX(Subdivisions!Z:Z,MATCH(AI872,Subdivisions!C:C,0)))) , "" ,INDEX(Subdivisions!Z:Z,MATCH(AI872,Subdivisions!C:C,0))),"")