MrExcel.com - Your One Stop for Excel Tips & Solutions

Adding a blank or hypen to a formula

Posted by Lars on July 24, 2001 11:09 AM

How do i add a "-" to this formula so if it doesn't find anything it doesn't add an N/A?

=index('Rank FLL'!$A$1:$G$6441,MATCH($O3,'RANK FLL'!$J$1:$J$6000,),MATCH($AD$1,'RANK FLL'!$A$1:$G$1,))


Thanks
Lars

Posted by Barrie Davidson on July 24, 2001 11:21 AM


Hi Lars, try changing your formula to:
=IF(ISNA(index('Rank FLL'!$A$1:$G$6441,MATCH($O3,'RANK FLL'!$J$1:$J$6000,),MATCH($AD$1,'RANK FLL'!$A$1:$G$1,))),"-",index('Rank FLL'!$A$1:$G$6441,MATCH($O3,'RANK FLL'!$J$1:$J$6000,),MATCH($AD$1,'RANK FLL'!$A$1:$G$1,)))

Regards,
Barrie

Posted by faster on July 24, 2001 11:24 AM

=IF(ISERROR(INDEX('Rank FLL'!$A$1:$G$6441,MATCH($O3,'Rank FLL'!$J$1:$J$6000,),MATCH($AD$1,'Rank FLL'!$A$1:$G$1,)))=TRUE,"-",INDEX('Rank FLL'!$A$1:$G$6441,MATCH($O3,'Rank FLL'!$J$1:$J$6000,),MATCH($AD$1,'Rank FLL'!$A$1:$G$1,)))

Posted by lars Thanks How do I not show blank info? on July 24, 2001 11:33 AM

That one I knew but I was hoping to not have to repeat the formula again. Is there an isnumber one I can use

if(Isnumber(index.....)),"-")

Thanks
Lars

Posted by Barrie Davidson on July 24, 2001 11:44 AM

There is an ISNUMBER function but you would still have to repeat the formula in your IF statement (sorry, no way around that one).

Barrie :) That one I knew but I was hoping to not have to repeat the formula again. Is there an isnumber one I can use if(Isnumber(index.....)),"-") Thanks

Posted by IML on July 24, 2001 12:28 PM

Hi Barrie,
I was just thinking, would there be any benefit to using a countif? Using Vlookup for example:
=IF(COUNTIF(A2:A4,"tree")>0,VLOOKUP("tree",A2:B4,2,FALSE),"")

This may be a way aroung the double looking up. Maybe shorter, maybe not better???

There is an ISNUMBER function but you would still have to repeat the formula in your IF statement (sorry, no way around that one). Barrie :) : That one I knew but I was hoping to not have to repeat the formula again. Is there an isnumber one I can use : if(Isnumber(index.....)),"-") : Thanks

Posted by Aladin Akyurek on July 24, 2001 12:51 PM

Adding my 2 cents too...


=IF(AND(ISNUMBER(MATCH($O3,'RANK FLL'!$J$1:$J$6000)),ISNUMBER(MATCH($AD$1,'RANK FLL'!$A$1:$G$1))),index('Rank FLL'!$A$1:$G$6441,MATCH($O3,'RANK FLL'!$J$1:$J$6000),MATCH($AD$1,'RANK FLL'!$A$1:$G$1)),"-")

Aladin