nested if statment - needs if(isna

[techiemom60]

I have the following formula, which works fine. It returns either the date, or the word current if the date is <=5 from today.

IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))<=5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))

My issue is that if there is no date for the selection in my vlookup, it returns #n/a. I would like to get rid of that.

I have added the if(isna(vlookup...), however, then it returns either the word True to False.

I have also tried =if(isna(IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))<=5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE))), with no results.

Any assistance would be greatly appreciated.

[Peo Sjoblom]

First you can remove 8 parenthesis, then use something like

=IF(ISNUMBER(MATCH(A3,Cons!$C$3:$C$126,0)),IF(TODAY()-VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)<=5,"Current",VLOOKUP(A3,Cons!$C$3:$F$8770,4,FALSE)),"Not
N/A")

don't know why you are using the whole range in the lookup, do you enter
this formula in a 124 row array?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"techiemom60" <[email protected]>
wrote in message
news:[email protected]...
>
> I have the following formula, which works fine. It returns either the
> date, or the word current if the date is <=5 from today.
>
> IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))<=5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))
>
> My issue is that if there is no date for the selection in my vlookup,
> it returns #n/a. I would like to get rid of that.
>
> I have added the if(isna(vlookup...), however, then it returns either
> the word True to False.
>
> I have also tried
> =if(isna(IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))<=5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE))),
> with no results.
>
> Any assistance would be greatly appreciated.
>
>
> --
> techiemom60
> ------------------------------------------------------------------------
> techiemom60's Profile:
> http://www.excelforum.com/member.php...o&userid=20124
> View this thread: http://www.excelforum.com/showthread...hreadid=526351
>

[Trevor Shuttleworth]

Try

=IF(ISNA(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)),"",IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))<=5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE))))

Regards

Trevor


"techiemom60" <[email protected]>
wrote in message
news:[email protected]...
>
> I have the following formula, which works fine. It returns either the
> date, or the word current if the date is <=5 from today.
>
> IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))<=5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))
>
> My issue is that if there is no date for the selection in my vlookup,
> it returns #n/a. I would like to get rid of that.
>
> I have added the if(isna(vlookup...), however, then it returns either
> the word True to False.
>
> I have also tried
> =if(isna(IF(((TODAY())-(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE)))<=5,"Current",(VLOOKUP($A$3:$A$126,Cons!$C$3:$F$8770,4,FALSE))),
> with no results.
>
> Any assistance would be greatly appreciated.
>
>
> --
> techiemom60
> ------------------------------------------------------------------------
> techiemom60's Profile:
> http://www.excelforum.com/member.php...o&userid=20124
> View this thread: http://www.excelforum.com/showthread...hreadid=526351
>

[techiemom60]

Thank you both. This was very helpful.