Nested If Formula - Please Help!!!

[Zedrick13]

How do I write the formula for a nested if function if the 3rd condition I want to write is ...,IF(B2=""n"No data"))) which means ...IF(B2 is blank, then show "No data").

My idea for the formula is like this: =IF($A$2<=B2,"Valid",IF($A$2>B2,"Expired",IF(B2="","No data")))

And, the table should look like this:
Date Today_Expiration Date_Status
13/09/18__20/01/2019_(Status - Valid,Expired,No data)
13/09/18__12/09/2018_(Status)
13/09/18__ _(Status)

The BLANK value is intentional. I want the formula to put "No data" in C3 for the status if B2 is blank.

Thanks!

 

[WaterGypsy]

I think your first 2 conditions will cover everything .... A2 is either <= B2 or it is > B2 .... if you want to check for B2 being blank then surely it needs to be the first condition ... or have I missed something ?

 

[sparky2205]

You can use =IF(ISBLANK(B2),"No data").
If B2 contains a formula, which returns FALSE for ISBLANK, then you can use =IF(COUNTBLANK(B2)>0, "No data"). COUNTBLANK ignores formulas.

But as WaterGypsy says that condition should be first. No point in evaluating the other conditions if B2 is blank.

 

[Zedrick13]

Thanks, sparky2205. You're a genius.

You can use =IF(ISBLANK(B2),"No data").
If B2 contains a formula, which returns FALSE for ISBLANK, then you can use =IF(COUNTBLANK(B2)>0, "No data"). COUNTBLANK ignores formulas.

But as WaterGypsy says that condition should be first. No point in evaluating the other conditions if B2 is blank.

 

[Zedrick13]

Thank you very much!

 

[sparky2205]

You're very welcome. Glad I could help.

 

[Zedrick13]

Can you please help me also with coming up with a formula combining IF and VLOOKUP. I want to use IF to combining a criteria that if VLOOKUP doesn't return a match from the table array, it shows ""(blank) on the cell containing the formula. But, if VLOOKUP returns a result, it shows the match in the cell containing the formula. It's like this:

FORMULA: =IF(No match,"",IF(B2<>"",VLOOKUP(B2,Sheet2!$A$2:$B$5,2,FALSE)))

NOTE: I just made up "No match" in the formula above as an illustration. That's the part I need help with, I guess.

Then, the sheet should look like this with the formula on the second column of Sheet 1:

Sheet1
[TABLE="width: 209"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Company[/TD]
[TD]Contact Person[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD] Hannah[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD] Robert[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD] David[/TD]
[/TR]
[TR]
[TD]Company E[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 209"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Company[/TD]
[TD]Contact Person[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD] Hannah[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD] Robert[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD] David[/TD]
[/TR]
[TR]
[TD]Company D[/TD]
[TD] Larry[/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

You could try using

=IFERROR(VLOOKUP(a1,b1:E10,2,0),"")

Change part in red to your VLOOKUP arguments