One way:

=IF(COUNTIF(ALLYEAR!$A$2:$A$366,C$2),IF(COUNTIF(OF FSET(ALLYEAR!$A$2,
MATCH(C$2,ALLYEAR!$A$2:$A$366,0)-1,$J3+6,1,5),$B3),"HOL",""),"")




In article ,
Caveman wrote:

I have a vlookup fomula, (the formula below works perfectly but
desperatly needs reducing in the use of IF statements)
=IF($B3=VLOOKUP(C$2,ALLYEAR!$A$2:$N$366,$J3+6),"HO L",
IF($B3=VLOOKUP(C$2,ALLYEAR!$A$2:$N$366,$J3+7),"HOL ",
IF($B3=VLOOKUP(C$2,ALLYEAR!$A$2:$N$366,$J3+8),"HOL ",
IF($B3=VLOOKUP(C$2,ALLYEAR!$A$2:$N$366,$J3+9),"HOL ",
IF($B3=VLOOKUP(C$2,ALLYEAR!$A$2:$N$366,$J3+10),"HO L",
VLOOKUP(C$2,ALLYEAR!$A$2:$E$366,$J3+1))))))
As i can only use 7 IF statements in a nested formula i need to
simplify the above, so that i can expand the formula in the future with
more if statements if necessary.
The formula searches for peoles names from another worksheet and if
they are listed to be on holiday at a specific date then the result
will show "HOL" against thier name.