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]...

 

[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]...

 

[techiemom60]

Thank you both. This was very helpful.