Remember Me?

#1
March 26th 06, 10:28 PM posted to microsoft.public.excel.worksheet.functions
 techiemom60 Posts: n/a
nested if statment - needs if(isna

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\$877 0,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\$877 0,4,FALSE))),

Any assistance would be greatly appreciated.

--
techiemom60
------------------------------------------------------------------------
techiemom60's Profile: http://www.excelforum.com/member.php...o&userid=20124

#2
March 26th 06, 10:52 PM posted to microsoft.public.excel.worksheet.functions
 Peo Sjoblom Posts: n/a
nested if statment - needs if(isna

First you can remove 8 parenthesis, then use something like

=IF(ISNUMBER(MATCH(A3,Cons!\$C\$3:\$C\$126,0)),IF(TODA Y()-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

Portland, Oregon

"techiemom60"
wrote in message
...

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\$877 0,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\$877 0,4,FALSE))),

Any assistance would be greatly appreciated.

--
techiemom60
------------------------------------------------------------------------
techiemom60's Profile:
http://www.excelforum.com/member.php...o&userid=20124

#3
March 26th 06, 10:57 PM posted to microsoft.public.excel.worksheet.functions
 Trevor Shuttleworth Posts: n/a
nested if statment - needs if(isna

Try

=IF(ISNA(VLOOKUP(\$A\$3:\$A\$126,Cons!\$C\$3:\$F\$8770,4,F ALSE)),"",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\$877 0,4,FALSE))))

Regards

Trevor

"techiemom60"
wrote in message
...

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\$877 0,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\$877 0,4,FALSE))),

Any assistance would be greatly appreciated.

--
techiemom60
------------------------------------------------------------------------
techiemom60's Profile:
http://www.excelforum.com/member.php...o&userid=20124

#4
March 27th 06, 08:00 PM posted to microsoft.public.excel.worksheet.functions
 techiemom60 Posts: n/a
nested if statment - needs if(isna

Thank you both. This was very helpful.

--
techiemom60
------------------------------------------------------------------------
techiemom60's Profile: http://www.excelforum.com/member.php...o&userid=20124

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post KG Excel Discussion (Misc queries) 2 September 10th 05 11:51 AM chiefdean13 Excel Discussion (Misc queries) 1 July 20th 05 05:45 AM John Simons Excel Worksheet Functions 14 February 16th 05 07:17 AM JIM.H. Excel Discussion (Misc queries) 2 December 31st 04 10:17 PM sthompson Setting up and Configuration of Excel 1 December 15th 04 07:38 PM

All times are GMT +1. The time now is 06:37 AM.