Implementation of OFFSET works inconsistently

Hello

Worked example attached - this formula sometimes works and sometimes does not. Can anyone assist and tell me what I am doing incorrectly? See below for problem definition and background.

PROBLEM
This formula seems to be inconsistent. Obviously there is an error in the way I have implemented it from the trial example, which worked.
Why is block C195-C208 in Revised CAPEX (Result Sheet) not picking-up the most recent data from BOQ CAPEX (Source data table) as determined by the most recent date, yet Cell C229-C242 is picking-up the most recent date set and Cell C773-C786 is also not working. This formula is simply copied down.

Background
In the attached file, there are two sheet:Revised CAPEX (Result Sheet) and BOQ CAPEX (Source data table)
Refer to Cell C195 which is then copied down to row 786.
=IFERROR(OFFSET('BOQ CAPEX'!$D$4,MATCH($H195,'BOQ CAPEX'!$C$4:$C$527,0)-2+COUNTIF($H$195:$H195,$H195)-0,COUNTA(OFFSET('BOQ CAPEX'!$E$3,MATCH($H195,'BOQ CAPEX'!$C$4:$C$527,0)-2,0,1,14))+1),"")

The result in Cell C195 captures data from the range in BOQ CAPEX (Source data table), depending upon two variable parameters in Revised CAPEX (Result Sheet): PO Number (Column C) and Cost Category (Column B). Further, the data set that is picked-up from BOQ CAPEX (Source data table), is always the most up to date data as defined by the date.

A solution to this problem would be greatly appreciated.

Thanks/David

Hello Pauleyb

Still a problem...

Thanks for your contribution. It is now returning the right numbers, however there is still something wrong. If there is no numeric in column E of BOQ CAPEX, which is the column in which the first set of data can be entered, it in fact returns the text in Column-D. I would like it just to return zero (0).

Your input is appreciated.

Best / David

Hello

File attached (version 1.2) with the error in sheet Revised CAPEX starting row E138.

I see the problem here, but I do not know how to fix it. I have attached an update with the error starting Cell E138 of BOQ CAPEX.

If the date is missing in the yellow row in Column-E, which is every 14, or 15th row then it seems to return Column-D from BOQ CAPEX into Column C of Revised CAPEX. Refer to Cell E138 of BOQ CAPEX. It now returns a blank C348-C361 in sheet Revised CAPEX. If one removes the date from, the column will return the cost categories which cannot happen. Further, if I delete a date from the top of a list of numbers and then insert the date to the next right hand column such that there is a date at the top of the column, but no numbers then it will return the numbers from the previous column.

Ideally, it should work like this: data is on retuned from BOQ CAPEX if there is cost data in rows in Column-E and any columns to the right and only if there is a date in the yellow row above it. And it should always fetch the cost data under the most recent date.

Would you know how to fix this. I will not always remember to include a date just to stop this from happening.

Help appreciated.

DB