Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. -- Caveman ------------------------------------------------------------------------ Caveman's Profile: http://www.excelforum.com/member.php...o&userid=27546 View this thread: http://www.excelforum.com/showthread...hreadid=470746 |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic VLOOKUP function | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions | |||
Regarding IF function or vLOOKUP function | Excel Worksheet Functions |