There are three sheets:

  1. The master sheet
  2. The 2020 figures
  3. The 2021 figures

The layout of the 2020 and 2021 sheets is like this:

Sample cells
Figure 1 showing codes in column A. In column B is a count of the total number of occurrences in that year using a simple COUNTA function.

My resuest is for a formula on the master sheet which currently looks like this:

enter image description here

Figure 2 showing the codes in column A. In column B is a count of the total number of occurences accross years, this is gained by adding the corresponding value in column D (the year 1 count) to the value in column F (the year 2 count).

Currently I have to manually enter the year 2 values to update them after working with the 2021 sheet. I would like a formula in F cells that finds the row specified by column A in the sheet specified by column E, and then take and show the B value from the 2021 sheet.

Does this make sense? How do I do this?


I've now tried the following VLOOKUP formula but I'm getting an error and I can't tell why it won't work:


  • Your codes are different. On the master sheet, some have leading zeros while on the years' sheets, they don't. Oct 9, 2021 at 13:27
  • Can there be any date of any year in the Date columns of Table 1? If not, please fill in some representative data. Oct 10, 2021 at 12:18


You must log in to answer this question.