Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Mar 12, 2022
Solved

IF Statement help. If Null return Null

Hello, if A11 is null then how could I return nothing (meaning nothing in the cell..ie null).  In the below, if A11 is null then it returns "Not In a DD"

 

=IF(ISNA(VLOOKUP(A11&B11&C11,ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Vendor]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice '#]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD")

 

thank you.

excel
  • houghtonap's avatar
    houghtonap
    Mar 17, 2022

    Yup, that changes things.


    =IF(TRIM(CLEAN(A11))="","",IF(ISNA(VLOOKUP(A11&B11&C11,ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Vendor]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice '#]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD"))

7 Replies

  • houghtonap's avatar
    houghtonap
    Copper Contributor
    Mar 13, 2022

    You need to be careful with the word "null". In Excel #NULL! is an error or someone could be referring to the contents of a cell not containing a value. The two are distinctly different in how they are handled. I'm going to assume you meant the content of the cell does not contain a value. The following should produce what you need:


    =IF(ISNA(VLOOKUP(IF(A11="",NA(),A11)&B11&C11,ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Vendor]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice '#]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD")

     

    If you really meant that it has the error #NULL! in cell A11 then this should work:


    =IF(ISNA(VLOOKUP(IFERROR(A11,NA())&B11&C11,ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Vendor]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice '#]]&ProjectCostsPQ_FE.xlsx!Project_Costs[[#All],[Invoice amount]],1,FALSE)),"NOT IN a DD","In Previous DD")

    • Tony2021's avatar
      Tony2021
      Iron Contributor
      Mar 13, 2022
      Hi houghtonap, thank you for the response.
      I tested and it seems to return "In Previous DD" if A11 is blank. Do you have another suggestion?

      thanks for the tip. I wasnt exactly sure how to convey what I wanted to say but yes you were correct.
      • houghtonap's avatar
        houghtonap
        Copper Contributor
        Mar 14, 2022
        Are you sure that cell A11 is actually empty and it doesn't contain spaces? If for some reason it does contain spaces, the change the formula to

        IF(TRIM(A11)="",NA(),A11)

        I tested this by doing the following:
        $A1 =IFERROR(IF(TRIM(A11)="",NA(),A11)&B11&C11,"EMPTY")
        $A11 =""
        $B11 ="ABC"
        $C11 ="DEF"

        When cell A11 is empty or spaces, then cell A11 has "EMPTY", otherwise it has cells $A11:$C11 concatenated together.

Resources