Index Match Formula Returning Incorrect Values

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

Formula:

Please Login or Register  to view this content.

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.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are new here, I shall do it for you this once: https://www.mrexcel.com/board/thread...g-n-a.1256008/)

Thanks Jeff! I don't know why it hadn't crossed my thoughts to unmerge on both tabs!

You need to acknowledge post #3 - have you read and understood it?

Thank you.

Originally Posted by hconn

Thanks Jeff! I don't know why it hadn't crossed my thoughts to unmerge on both tabs!

You must unmerge and repeat each date in three columns (you could use an = formula). Do you have it working? If so, please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.

Better formula for the first monday of the month.

Formula:

Please Login or Register  to view this content.

And this is the formula for you to use in row 4 as 6StringJazzer said.

Formula:

Please Login or Register  to view this content.