If and Blanks provide data

[Jmayfield4]

Okay all, I have tried so many things to get this to work and its not. So here I am asking for help again!

Above is my table (Figure 1) with the column heads, let's all just pretend that the very first line of data is row 2.

This is lookup table (Figure 2).


What I need is a formula to check and see if Months_Between has data, if no data, then check and see if Date_Hired_2 has data, if no data, return "Vacant". Now, if Months_Between has data, then use Lookup formula, to match range value of Months_Between in figure 2 and return the corresponding data. If Months_Between has no data, but Date_Hired_2 has data, return "Active".

So, what I think, and it has not been working is:

=IF(AND(ISBLANK(Months_Between),ISBLANK(Date_Hired_2)),"Vacant",IF(ISBLANK(Months_Between),"Active",LOOKUP(Months_Between),Figure2!$A$1:$A$4,Figure2!$B$1:$B$4)))

Any help????

 

[pjmorris]

I think this works (it appeared to do so for me):

=IF(AND([@[Term Date 2]]="",[@[Date Hired]]=""), "Vacant", IF([@[Months Between]]<>"", VLOOKUP([@[Months Between]],$R$2:$S$5,2,FALSE),IF([@[Date Hired]]<>"","Active","")))

HTH

 

[Jmayfield4]

I think this works (it appeared to do so for me):

=IF(AND([@[Term Date 2]]="",[@[Date Hired]]=""), "Vacant", IF([@[Months Between]]<>"", VLOOKUP([@[Months Between]],$R$2:$S$5,2,FALSE),IF([@[Date Hired]]<>"","Active","")))

HTH

This worked but just had to change from VLOOKUP to LOOKUP and use the absolute ranges. Thank you so much for your help!! I spent a majority of the day trying various formulas to get this to work.

 

[pjmorris]

glad it helped - I've not used LOOKUP, but will probably do so now you've reminded me of it!