Help Please

[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????

 

[hajiali]

I think this is what your needing try

Book1
	D	E	F	G
1	Date Hired 2	Term Date 2	Months Between
2	16-Jan			Active
3			6-Jan	6-11 Months
4		16-Jan		Vacant
5
6
7
8		0	<6 Months
9		6	6-11 Months
10		12	12-36 Months
11		36	> 36 Months
Sheet2

Cell Formulas
Range	Formula
G2:G4	G2	=IF(AND(ISBLANK(D2),ISBLANK(F2)),"Vacant",IF(F2<>"",INDEX($F$8:$F$11,MATCH(F2,$E$8:$E$11,0)),IF(AND(F2="",D2<>""),"Active","")))

 

[Peter_SSs]

Duplicate to: If and Blanks provide data

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.