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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 02:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com