Good afternoon,
I have a sheet that contains a list of electricity supply invoice lines (call it Data). Each row has "From" and "To" dates A second sheet contains a record of current and previous contract start and end dates and associated prices (call it Contracts). I want to look up the supply number from Data in Contracts and when found determine which of the Contracts has the correct pricing for the pair of From and To dates. I can get the formula below to work in a small test environment but when copied to my production environment it fails with #Value.
=IF(VLOOKUP($J114,contracts,1,FALSE),(LOOKUP(2,1/($K$2:$K$8<=$K114)/($L$2:$L$8>=$L114),M$2:M$8)))
Rows 1-8 below represent Contracts, the rest Data
Many thanks in advance for any help.
Denis
<colgroup><col><col><col span="2"><col><col span="4"></colgroup><tbody>
</tbody>
I have a sheet that contains a list of electricity supply invoice lines (call it Data). Each row has "From" and "To" dates A second sheet contains a record of current and previous contract start and end dates and associated prices (call it Contracts). I want to look up the supply number from Data in Contracts and when found determine which of the Contracts has the correct pricing for the pair of From and To dates. I can get the formula below to work in a small test environment but when copied to my production environment it fails with #Value.
=IF(VLOOKUP($J114,contracts,1,FALSE),(LOOKUP(2,1/($K$2:$K$8<=$K114)/($L$2:$L$8>=$L114),M$2:M$8)))
Rows 1-8 below represent Contracts, the rest Data
Many thanks in advance for any help.
Denis
MPAN | Supplier | Contract Start | Contract End | Contracted day unit charge: | Contracted night unit charge: | E&W/E | Contracted standing charge: | ASC: |
2000054798787 | BG | 00/01/1900 | 20/02/2014 | 9.01 | 0 | 61.91 | ||
2000054798787 | BG | 21/02/2014 | 19/02/2015 | 9.72 | 0 | 62.42 | ||
2000054798787 | EDF | 20/02/2015 | 30/06/2015 | 9.5614 | 0 | 61.0964 | ||
2000054798787 | EDF | 01/07/2015 | 30/06/2016 | 9.7094 | 0 | 48.439 | ||
2000054798787 | Hudson | 01/01/2016 | 31/12/2016 | 9.97 | 6.09 | 84.56589041 | ||
2000054798787 | EDF | 01/07/2016 | 11/07/2016 | 9.5748 | 0 | 46.1226 | ||
2000054798787 | EDF | 12/07/2016 | 30/06/2017 | 9.906 | 6.08 | 108.2095479 | 2.3499 | |
MPAN | From | TO | Day Rate | Night Rate | E&WE Rate | St Charge | ASC | |
2000054798787 | 28/11/2013 | 17/12/2013 | 9.0100 | 0.0000 | 0.0000 | 61.9100 | 0.0000 | |
2000054798787 | 18/12/2013 | 27/01/2014 | 9.0100 | 0.0000 | 0.0000 | 61.9100 | 0.0000 | |
2000054798787 | 28/01/2014 | 21/02/2014 | #N/A | #N/A | #N/A | #N/A | #N/A | |
2000054798787 | 22/02/2014 | 28/02/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/03/2014 | 31/03/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/04/2014 | 23/04/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 24/04/2014 | 31/05/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/06/2014 | 30/06/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/07/2014 | 31/07/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/08/2014 | 19/08/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 20/08/2014 | 01/10/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/10/2014 | 31/10/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/11/2014 | 30/11/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/12/2014 | 31/12/2014 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 | |
2000054798787 | 01/01/2015 | 31/01/2015 | 9.7200 | 0.0000 | 0.0000 | 62.4200 | 0.0000 |
<colgroup><col><col><col span="2"><col><col span="4"></colgroup><tbody>
</tbody>