simplify a vlookup function

  • Thread starter Thread starter Caveman
  • Start date Start date
C

Caveman

I have a vlookup fomula, (the formula below works perfectly bu
desperatly needs reducing in the use of IF statements)
=IF($B3=VLOOKUP(C$2,ALLYEAR!$A$2:$N$366,$J3+6),"HOL",
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),"HOL",
VLOOKUP(C$2,ALLYEAR!$A$2:$E$366,$J3+1))))))
As i can only use 7 IF statements in a nested formula i need t
simplify the above, so that i can expand the formula in the future wit
more if statements if necessary.
The formula searches for peoles names from another worksheet and i
they are listed to be on holiday at a specific date then the resul
will show "HOL" against thier name. :rolleyes
 
One way:

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