edit formula to not show 0 in cell

route217
route217 used Ask the Experts™
on
Hi experts using excel 2010

I am using the following formula in a cell and keep on getting 0 in the cell and I just want show blank...what's wrong..

Formula.Array
{=PROPER(VLOOKUP(C9,CHOOSE({1,2},TABLE1 [0],TABLE1[PROJECT]),2,0),""))}
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Because perhaps the match found is zero
So if error cannot help here

So you could add at the beginning IF. Then entire formula inside the parentheses = 0 then , ""
Top Expert 2016
Commented:
Hi,

You could change the format ( in Format cells / Custom) to

#'##0;-#'##0;"";@

1st element is positive value, 2nd is neg value, 3rd 0 and 4th text

Regards
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
like this

=if(PROPER(VLOOKUP(C9,CHOOSE({1,2},TABLE1[0],TABLE1[PROJECT]),2,0),"")=0,"",PROPER(VLOOKUP(C9,CHOOSE({1,2},TABLE1[0],TABLE1[PROJECT]),2,0),""))
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

route217Junior

Author

Commented:
Thanks for the excellent feedback...and prof Jim jam...that edit formula not working..
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
did you enter with control shift enter?

can you try this

=if((PROPER(VLOOKUP(C9,CHOOSE({1,2},TABLE1 [0],TABLE1[PROJECT]),2,0),""))=0,"",PROPER(VLOOKUP(C9,CHOOSE({1,2},TABLE1 [0],TABLE1[PROJECT]),2,0),"")))
Please use below formula.

=IFERROR(INDEX(Table1[Project],MATCH(C9,Table1[0],0)),"")
Microsoft Excel Expert
Top Expert 2014
Commented:
please ignore my earlier post

this is tested formula ENTER THIS FORMULA   with control shift enter

=IF(PROPER(VLOOKUP(C9,CHOOSE({1,2},Table1[0],Table1[PROJECT]),2,0))=0,"",PROPER(VLOOKUP(C9,CHOOSE({1,2},Table1[0],Table1[PROJECT]),2,0)))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial