MATCH or LOOKUP formulae required!

[Allan Walker]

Hi Everyone,

Hope you are enjoying your holidays and have had a nice Christmas Day.

I'm a little stuck and hope someone can help me with with a formula (or formulae)!

Here's my "results" table that holds the formulae:

Results

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 208px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 73px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left">YASMIN AKHTAR (YA1)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left">Type</TD><TD style="TEXT-ALIGN: left">15 mins</TD><TD style="TEXT-ALIGN: left">30 mins</TD><TD style="TEXT-ALIGN: left">45 mins</TD><TD style="TEXT-ALIGN: left">60 mins</TD><TD style="TEXT-ALIGN: left">Total Hours</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left">Week Day</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">WeekEnd</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">Bank Holiday</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">Totals</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">JOSEPHINE ANSAH (JA1)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">Type</TD><TD style="TEXT-ALIGN: left">15 mins</TD><TD style="TEXT-ALIGN: left">30 mins</TD><TD style="TEXT-ALIGN: left">45 mins</TD><TD style="TEXT-ALIGN: left">60 mins</TD><TD style="TEXT-ALIGN: left">Total Hours</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">Week Day</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">WeekEnd</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">Bank Holiday</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">Totals</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A1</TD><TD>='14 Day Report'!A1</TD></TR><TR><TD>A2</TD><TD>='14 Day Report'!A2</TD></TR><TR><TD>B2</TD><TD>='14 Day Report'!B2</TD></TR><TR><TD>C2</TD><TD>='14 Day Report'!C2</TD></TR><TR><TD>D2</TD><TD>='14 Day Report'!D2</TD></TR><TR><TD>E2</TD><TD>='14 Day Report'!E2</TD></TR><TR><TD>F2</TD><TD>='14 Day Report'!F2</TD></TR><TR><TD>A3</TD><TD>='14 Day Report'!A3</TD></TR><TR><TD>A4</TD><TD>='14 Day Report'!A4</TD></TR><TR><TD>A5</TD><TD>='14 Day Report'!A5</TD></TR><TR><TD>A6</TD><TD>='14 Day Report'!A6</TD></TR><TR><TD>A7</TD><TD>='14 Day Report'!A7</TD></TR><TR><TD>A8</TD><TD>='14 Day Report'!A8</TD></TR><TR><TD>B8</TD><TD>='14 Day Report'!B8</TD></TR><TR><TD>C8</TD><TD>='14 Day Report'!C8</TD></TR><TR><TD>D8</TD><TD>='14 Day Report'!D8</TD></TR><TR><TD>E8</TD><TD>='14 Day Report'!E8</TD></TR><TR><TD>F8</TD><TD>='14 Day Report'!F8</TD></TR><TR><TD>A9</TD><TD>='14 Day Report'!A9</TD></TR><TR><TD>A10</TD><TD>='14 Day Report'!A10</TD></TR><TR><TD>A11</TD><TD>='14 Day Report'!A11</TD></TR><TR><TD>A12</TD><TD>='14 Day Report'!A12</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Then there is a 14 day (will soon be 28 day) report, which is the main report. Note "Bank Holiday" hours is always zero. This is because the mySQL database doesn't recognise Bank Holidays, so it is an added row.

14 Day Report

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 208px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 73px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>YASMIN AKHTAR (YA1)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Type</TD><TD>15 mins</TD><TD>30 mins</TD><TD>45 mins</TD><TD>60 mins</TD><TD>Total Hours</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Week Day</TD><TD style="TEXT-ALIGN: right">35</TD><TD style="TEXT-ALIGN: right">57</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">56.75</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>WeekEnd</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">22</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Bank Holiday</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Totals</TD><TD style="TEXT-ALIGN: right">54</TD><TD style="TEXT-ALIGN: right">86</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">78.75</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>JOSEPHINE ANSAH (JA1)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Type</TD><TD>15 mins</TD><TD>30 mins</TD><TD>45 mins</TD><TD>60 mins</TD><TD>Total Hours</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Week Day</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">13.25</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>WeekEnd</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">12.5</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Bank Holiday</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Totals</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">34</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">25.75</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Then I have six Bank Holiday worksheets - bank holiday 1 on a week day, bank holiday 1 on a week end, bank holiday 2 on a week day, bank holiday 2 on a week end, bank holiday 3 on a week day, and bank holiday 3 on a week end. This takes care of the permutations of Bank Holidays in the UK - for example, Xmas Day, Boxing Day, and New Years day.

Bank Holiday 1 on a Week Day

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 208px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 73px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>YASMIN AKHTAR (YA1)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Type</TD><TD>15 mins</TD><TD>30 mins</TD><TD>45 mins</TD><TD>60 mins</TD><TD>Total Hours</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Bank Holiday 1 on a Week Day</TD><TD style="TEXT-ALIGN: right">35</TD><TD style="TEXT-ALIGN: right">57</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">56.75</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>JOSEPHINE ANSAH (JA1)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Type</TD><TD>15 mins</TD><TD>30 mins</TD><TD>45 mins</TD><TD>60 mins</TD><TD>Total Hours</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Bank Holiday 1 on a Week Day</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">13.25</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


The intent is for the user to run a 14 day, and a one day report per bank holiday. The bank holiday data is a subset of the 14 day report.

So what I am after is - in Results - a formula that matches or looks up the name (as the report can contract or expand), take off the week day/end hours from the 14 day from the relevant bank holiday sheets, and a formula that looks up the name and populates the bank holiday hours.

Best Regards,

Allan