• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

My IFNA function IF(VLOOKUP) I cannot solve

Nathalye

New Member
Dear All. I am with a bit of a problem and for a while now I have floundered and cannot find a solution. So, I've attached a worksheet that explains exactly what I want to accomplish. Please suggest best solution, best practice the formula in cell V is not functioning properly.

=IF(OR(D4="TBD",N4="No",F4="No data",AND(F4="Concept",L4<=$L$1),AND(F4="Definition",L4<=$L$1),AND(F4="Agile Planning",L4<=$L$1)),"N/A",IFNA(IF(T4="N", "Need Key Task",IF(VLOOKUP(D4,Table18[#All],26,FALSE)="Yes",VLOOKUP(D4,Table18[#All],29,FALSE),"Task Not Baselined")),"No data"))

Thank you in advance!

~Thayle
 

Attachments

  • Project Audit 2015 help.xlsx
    996.8 KB · Views: 10
Hi ,

I do not know where everything is explained ; can you put down your explanation in a post in this thread ?

As far as I can see ( I don't have Excel 2013 , but I have used the ISNA function instead ) , the formula works ; can you explain why you think it does not work correctly ?

Narayan
 
Thayle

Firstly, Welcome to the Chandoo.org Forums

What is IFNA ?

If you had a user defined function IFNA() the file must be saved as either a *.xlsm or *.xlsb file type
 
@Hui

It's new function available only in Excel 2013 or later. Much like IFERROR introduced for Excel 2007.
Syntax: IFNA( value, value_if_na )
 
Thank you, great Excel Ninja's for coming to my aid. Let me clarify my situation. The formula in cell U4 is not returning the correct lookup data. When cell T4 has a value of 1, cell U4 should return a date, '12/30/15' from cell, but cell 'AD' but it returns ‘task not baselined’.

Thank you again, for all direction and guidance. I donate to all worthy causes. Please let me know how/where link to donate on behalf of my request for aid. Blessings.

=IF(OR(D4="TBD",N4="No",F4="No data",AND(F4="Concept",L4<=$L$1),AND(F4="Definition",L4<=$L$1),AND(F4="Agile Planning",L4<=$L$1)),"N/A",IFNA(IF(T4="N", "Need Key Task",IF(VLOOKUP(D4,Table18[#All],26,FALSE)="Yes",VLOOKUP(D4,Table18[#All],29,FALSE),"Task Not Baselined")),"No data"))

upload_2015-10-2_20-37-7.png upload_2015-10-2_20-37-27.png
 
Hi ,

I do not know where everything is explained ; can you put down your explanation in a post in this thread ?

As far as I can see ( I don't have Excel 2013 , but I have used the ISNA function instead ) , the formula works ; can you explain why you think it does not work correctly ?

Narayan

Thank you, Narayan for coming to my aid. Let me clarify my situation. The formula in cell U4 is not returning the correct lookup data. When cell T4 has a value of 1, cell U4 should return a date, '12/30/15' from cell, but cell 'AD' but it returns ‘task not baselined’.

Thank you again, for all direction and guidance. I donate to all worthy causes. Please let me know how/where link to donate on behalf of my request for aid. Blessings.

=IF(OR(D4="TBD",N4="No",F4="No data",AND(F4="Concept",L4<=$L$1),AND(F4="Definition",L4<=$L$1),AND(F4="Agile Planning",L4<=$L$1)),"N/A",IFNA(IF(T4="N", "Need Key Task",IF(VLOOKUP(D4,Table18[#All],26,FALSE)="Yes",VLOOKUP(D4,Table18[#All],29,FALSE),"Task Not Baselined")),"No data"))

View attachment 22937 View attachment 22938
 
Hi ,

Your formula is not implementing the logic that you have outlined.

1. The column you are referring to is not T but U.

2. It is column U which has numeric values such as 1 , and the formula you have posted ( in column V ) does not refer to the values in column U.

3. The column AA in the Table18 has the text Yes only in one record for the Project ID , which is why the VLOOKUP function will not work the way you expect it to.

You need to change the formula to :

=IF(OR(D4="TBD",N4="No",F4="No data",AND(F4="Concept",L4<=$L$1),AND(F4="Definition",L4<=$L$1),AND(F4="Agile Planning",L4<=$L$1)),"N/A",IF(ISNA(IF(T4="N", "Need Key Task",IF(U4=0,"Task Not Baselined",IF(SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date])=0,"",SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date]))))),"No data",IF(T4="N", "Need Key Task",IF(U4=0,"Task Not Baselined",IF(SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date])=0,"",SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date]))))))

where I have used the ISNA function , since I do not have Excel 2013. In your case , you can use :

=IF(OR(D4="TBD",N4="No",F4="No data",AND(F4="Concept",L4<=$L$1),AND(F4="Definition",L4<=$L$1),AND(F4="Agile Planning",L4<=$L$1)),"N/A",IFNA(IF(T4="N", "Need Key Task",IF(U4=0,"Task Not Baselined",IF(SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date])=0,"",SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date])))),"No data"))

Narayan
 
Hi ,

Your formula is not implementing the logic that you have outlined.

1. The column you are referring to is not T but U.

2. It is column U which has numeric values such as 1 , and the formula you have posted ( in column V ) does not refer to the values in column U.

3. The column AA in the Table18 has the text Yes only in one record for the Project ID , which is why the VLOOKUP function will not work the way you expect it to.

You need to change the formula to :

=IF(OR(D4="TBD",N4="No",F4="No data",AND(F4="Concept",L4<=$L$1),AND(F4="Definition",L4<=$L$1),AND(F4="Agile Planning",L4<=$L$1)),"N/A",IF(ISNA(IF(T4="N", "Need Key Task",IF(U4=0,"Task Not Baselined",IF(SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date])=0,"",SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date]))))),"No data",IF(T4="N", "Need Key Task",IF(U4=0,"Task Not Baselined",IF(SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date])=0,"",SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date]))))))

where I have used the ISNA function , since I do not have Excel 2013. In your case , you can use :

=IF(OR(D4="TBD",N4="No",F4="No data",AND(F4="Concept",L4<=$L$1),AND(F4="Definition",L4<=$L$1),AND(F4="Agile Planning",L4<=$L$1)),"N/A",IFNA(IF(T4="N", "Need Key Task",IF(U4=0,"Task Not Baselined",IF(SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date])=0,"",SUMPRODUCT((Table18[[Project ID ]]=D4)*(Table18[Is Key Task]="Yes")*Table18[Task Baseline Finish Date])))),"No data"))

Narayan
 
Thank you, Great Excel Ninja for all your direction and gentle guidance. Excel is not accepting either of the formula's that you so graciously supplied. It is asking me to add an apostrophe at the beginning. Confused, I am. :)
 
Thank you, Narayan! You rock! Nice job. Blessings to you. You are not listed under the thank you donations. I will keep checking. Thanks again! ~Thayle
 
Back
Top