simplify a vlookup function
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. :rolleyes: -- Caveman ------------------------------------------------------------------------ Caveman's Profile: http://www.excelforum.com/member.php...o&userid=27546 View this thread: http://www.excelforum.com/showthread...hreadid=470746 |
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. :rolleyes: |
All times are GMT +1. The time now is 01:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com