Lookup value based on criteria

[cmb80]

Hi all, please find attachment enclosed

In cells B16, B18, B20 and B22 I want the dates to display by formula.

I want to find the first green cell for each product (lines 4,6,8,10) where there is a date populated in row 1.

As you can see in line 4, the first green cell is H4 - but there is no date in row 1. The first green cell with a date in row 1 is AV4 - so in cell B16 it displayed 23/02/2018.
Same for other 3 rows.

Can someone help with a formula to do this automatically?

[cmb80]

If it simplifies things, instead of referencing by colour green, it can use the cell with blank value as the "black cells" have letter f within them

[cmb80]

Is anyone able to assist with this request?

[JeteMc]

Using the fact that the black cells are not blank, as suggested in post #2, the following formula yields the same dates as those given in B16:B22

Please Login or Register  to view this content.

Let us know if you have any questions.

[JeteMc]

Here is an array entered formula* that is possibly more appropriate to the task:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.