Prev: copying a formula down wards
Next: Logical function please
From: cadustin on 15 Apr 2010 12:12
I have tried to correct this formula with the ISNA, and not getting any
result, please fix what I am doing wrong! I put the ISNA in the beginning and
am getting an error. I just want excel to return either a blank cell or a 0
if nothing has been entered. My formula works great other than the #N/A when
nothing is in my other fields to produce my formula.

=IF(ISNA($F$2="FMLA",INDEX('R:\Benefits\[Insurance Premiums for
leaves.xls]Look Up Sheet'!$B$6:$E$9,MATCH(B6,'R:\Benefits\[Insurance Premiums
for leaves.xls]Look Up Sheet'!$A$6:$A$9,0),MATCH(A6,'R:\Benefits\[Insurance
Premiums for leaves.xls]Look Up
Sheet'!$B$1:$E$1,0)),INDEX('R:\Benefits\[Insurance Premiums for
leaves.xls]Look Up Sheet'!$B$2:$E$5,MATCH(B6,'R:\Benefits\[Insurance Premiums
for leaves.xls]Look Up Sheet'!$A$2:$A$5,0),MATCH(A6,'R:\Benefits\[Insurance
Premiums for leaves.xls]Look Up Sheet'!$B$1:$E$1,0))))
From: Bernard Liengme on 15 Apr 2010 12:23
Show us the formula before you edited it.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"cadustin" <cadustin(a)discussions.microsoft.com> wrote in message
news:4ADFD6B2-CE97-455D-8341-BF512ADA8523(a)microsoft.com...
> I have tried to correct this formula with the ISNA, and not getting any
> result, please fix what I am doing wrong! I put the ISNA in the beginning
> and
> am getting an error. I just want excel to return either a blank cell or a
> 0
> if nothing has been entered. My formula works great other than the #N/A
> when
> nothing is in my other fields to produce my formula.
>
> =IF(ISNA($F$2="FMLA",INDEX('R:\Benefits\[Insurance Premiums for
> leaves.xls]Look Up Sheet'!$B$6:$E$9,MATCH(B6,'R:\Benefits\[Insurance
> Premiums
> for leaves.xls]Look Up
> Sheet'!$A$6:$A$9,0),MATCH(A6,'R:\Benefits\[Insurance
> Premiums for leaves.xls]Look Up
> Sheet'!$B$1:$E$1,0)),INDEX('R:\Benefits\[Insurance Premiums for
> leaves.xls]Look Up Sheet'!$B$2:$E$5,MATCH(B6,'R:\Benefits\[Insurance
> Premiums
> for leaves.xls]Look Up
> Sheet'!$A$2:$A$5,0),MATCH(A6,'R:\Benefits\[Insurance
> Premiums for leaves.xls]Look Up Sheet'!$B$1:$E$1,0))))

From: Duke Carey on 15 Apr 2010 12:42
maybe:

=IF($F$2="FMLA",IF(ISNA([your lengthy formula]),"",[your lengthy formula]),"")

"cadustin" wrote:

> I have tried to correct this formula with the ISNA, and not getting any
> result, please fix what I am doing wrong! I put the ISNA in the beginning and
> am getting an error. I just want excel to return either a blank cell or a 0
> if nothing has been entered. My formula works great other than the #N/A when
> nothing is in my other fields to produce my formula.
>
> =IF(ISNA($F$2="FMLA",INDEX('R:\Benefits\[Insurance Premiums for
> leaves.xls]Look Up Sheet'!$B$6:$E$9,MATCH(B6,'R:\Benefits\[Insurance Premiums
> for leaves.xls]Look Up Sheet'!$A$6:$A$9,0),MATCH(A6,'R:\Benefits\[Insurance
> Premiums for leaves.xls]Look Up
> Sheet'!$B$1:$E$1,0)),INDEX('R:\Benefits\[Insurance Premiums for
> leaves.xls]Look Up Sheet'!$B$2:$E$5,MATCH(B6,'R:\Benefits\[Insurance Premiums
> for leaves.xls]Look Up Sheet'!$A$2:$A$5,0),MATCH(A6,'R:\Benefits\[Insurance
> Premiums for leaves.xls]Look Up Sheet'!$B$1:$E$1,0))))
 | 
Pages: 1
Prev: copying a formula down wards
Next: Logical function please