A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

nested if statment - needs if(isna



 
 
Thread Tools Display Modes
  #1  
Old March 26th 06, 10:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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))),
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

Ads
  #2  
Old March 26th 06, 10:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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

(remove ^^ from email address)

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


  #3  
Old March 26th 06, 10:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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))),
> 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
>



  #4  
Old March 27th 06, 08:00 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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
View this thread: http://www.excelforum.com/showthread...hreadid=526351

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested Subtotals in Excel 2002 KG Excel Discussion (Misc queries) 2 September 10th 05 11:51 AM
Why are my nested sub-totals are displaying incorrectly? chiefdean13 Excel Discussion (Misc queries) 1 July 20th 05 05:45 AM
Nested IF statements John Simons Excel Worksheet Functions 14 February 16th 05 06:17 AM
Q: Nested if JIM.H. Excel Discussion (Misc queries) 2 December 31st 04 09:17 PM
nested ifs sthompson Setting up and Configuration of Excel 1 December 15th 04 06:38 PM


All times are GMT +1. The time now is 02:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.