Forum Discussion

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,ProjectCosts...

excel

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

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
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
  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.
  - houghtonap
    Copper Contributor
    Mar 14, 2022
    Sorry "When cell A11 is empty or spaces, then cell A11 has "EMPTY", "
    
    Should read: When cell A11 is empty or spaces, then cell A1 has "EMPTY",