The root cause of all your problems is that you are using merged cells, which is never a good idea.
When you merge cells, only the first cell contains the data for reference purposes. For example, you merged G2, H2, I2. G2 has the date. H2 and I2 are empty and will return the null string if referenced in formulas. You are referencing these empty cells, apparently assuming they also contain the date.
The formula in H4
The formula refers to H2, but H2 is part of merged cells (G2:I2) and so H2 is empty.
You have a similar problem on the data sheet. The dates are in merged cells. So the first date is in E4, and the cells it's merged with, F4 and G4, are empty.
So the MATCH expression is looking for a match on values in two different columns. It looks for a match of "CC" & <the date>. But as I described, the date that MATCH is looking for is always blank for CC and ACC, so it will find the first match of a blank date, which will always be column F for CC and G for ACC, no matter what date you see on the sheet.
To fix this, you must either:
1. Unmerge all the cells and repeat each date three times. This is what I would do.
2. Rewrite all your formulas to refer to the first cell in each set of merged cells, i.e., change H2 to G2. Then the formula in column I would change I2 to G2. Repeat for all subsequent formulas. Also change all the formulas to find the date first in row 4 of the data sheet, and then navigate from there to the appropriate item/service (BACS, CC, ACC) instead of doing one MATCH to find them both at once.
Bookmarks