Using VLOOKUP to Return a Formula Rather than a Value

[Puni19]

Hi,

I work on a helpdesk and am required to calculate incident duration to determine if we have achieved or missed SLA. The sites we provide service to have non-standard hours of business (hours of operation) and am required to calculate the duration of the incident, factoring in the open and close times of the site. I created a number of formulas that calculate the incident duration relative to the hours of operation and then nested all the formulas into an IF statement. I’m calculating hundreds of incidents and so far, so good, until the client changes the hours of operation for a site, or adds a new site with new hours of operation and I have to edit a long nested formula to accommodate.

My thought was to create a VLOOKUP table with the hours of operation as the lookup value and the formulas in the array so that I could add to or edit the table, rather than the nested IF formula. Playing around with the concept I can’t get it to work, I think as the formula is “hard written” (if that’s the right terminology) so when I perform the VLookup the formula is relative to the original design, not its place in the column I’m trying to populate. Tried to fix the cell reference and build the Lookup formula to include all columns but am failing.

Is it possible to perform a Vlookup to return a formula, which in turn will perform the calculation, rather than a value?

If not, are there other options available to achieve my outcome? Essentially I don’t want to have to edit the nested formula each time the client makes a change.

Excel 2010

	E	F	G	H	I
2	Hours of Operation	Start	Stop		Formula / Calculation
3	M-F (09:00 - 16:30)	14/04/2015 13:00	15/04/2015 16:30		660
4	M-F (09:00 - 16:30); S (10:00 - 13:00)	14/04/2015 13:00	14/04/2015 16:30		210
5	M-F (09:00 - 16:30); S (10:00 - 14:00)	14/04/2015 13:00	15/04/2015 16:30		660
6	M-T,R-F (09:00 - 16:30); W (09:00 - 18:30)	14/04/2015 13:00	15/04/2015 16:30		660
7	M-W,F (09:00 - 16:30); R (09:00 - 18:30)	14/04/2015 13:00	15/04/2015 16:30		660

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula I3 =(SUMPRODUCT(NETWORKDAYS.INTL(F3,G3,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,7.5,7.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(F3,2),7.5,7.5,7.5,7.5,7.5,0,0),MOD(F3,1)*24-CHOOSE(WEEKDAY(F3,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(G3,2),7.5,7.5,7.5,7.5,7.5,0,0),CHOOSE(WEEKDAY(G3,2),16.5,16.5,16.5,16.5,16.5,0,0)-MOD(G3,1)*24))*60 I4 =(SUMPRODUCT(NETWORKDAYS.INTL(F4,G4,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,7.5,7.5,7.5,3,0})-MEDIAN(0,CHOOSE(WEEKDAY(F4,2),7.5,7.5,7.5,7.5,7.5,3,0),MOD(F4,1)*24-CHOOSE(WEEKDAY(F4,2),9,9,9,9,9,10,0))-MEDIAN(0,CHOOSE(WEEKDAY(G4,2),7.5,7.5,7.5,7.5,7.5,3,0),CHOOSE(WEEKDAY(G4,2),16.5,16.5,16.5,16.5,16.5,13,0)-MOD(G4,1)*24))*60 I5 =(SUMPRODUCT(NETWORKDAYS.INTL(F5,G5,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,7.5,7.5,7.5,4,0})-MEDIAN(0,CHOOSE(WEEKDAY(F5,2),7.5,7.5,7.5,7.5,7.5,4,0),MOD(F5,1)*24-CHOOSE(WEEKDAY(F5,2),9,9,9,9,9,10,0))-MEDIAN(0,CHOOSE(WEEKDAY(G5,2),7.5,7.5,7.5,7.5,7.5,4,0),CHOOSE(WEEKDAY(G5,2),16.5,16.5,16.5,16.5,16.5,14,0)-MOD(G5,1)*24))*60 I6 =(SUMPRODUCT(NETWORKDAYS.INTL(F6,G6,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,9.5,7.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(F6,2),7.5,7.5,9.5,7.5,7.5,0,0),MOD(F6,1)*24-CHOOSE(WEEKDAY(F6,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(G6,2),7.5,7.5,9.5,7.5,7.5,0,0),CHOOSE(WEEKDAY(G6,2),16.5,16.5,18.5,16.5,16.5,0,0)-MOD(G6,1)*24))*60 I7 =(SUMPRODUCT(NETWORKDAYS.INTL(F7,G7,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,7.5,9.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(F7,2),7.5,7.5,7.5,9.5,7.5,0,0),MOD(F7,1)*24-CHOOSE(WEEKDAY(F7,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(G7,2),7.5,7.5,7.5,9.5,7.5,0,0),CHOOSE(WEEKDAY(G7,2),16.5,16.5,16.5,18.5,16.5,0,0)-MOD(G7,1)*24))*60 <tbody> </tbody>

<tbody>
</tbody>

Thanks,