Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

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


Re: Adding a blank or hypen to a formula

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


Re: Adding a blank or hypen to a formula

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,)))


Re: Adding a blank or hypen to a formula

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


Re: Adding a blank or hypen to a formula

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


Re: Adding a blank or hypen to a formula

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


Adding my 2 cents too...

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


=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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.