I am having a significant mind blank when trying to suss this one out.

I want to return the date from a few rows above if a cell contains a certain value.

Image 1: Image 1
Image 2: How I want it to look Image 2

I tried using a similar sum to the one in this thread… but it didn’t work: Excel: Check if Cell value exists in Column, and return a value in the same row but different column

>Solution :

Assuming the values in column F increase through to the end of the year (week 1, week 2, etc)

The formula you are looking for is XLOOKUP. This allows you specify a value to search for ("N"), the array of values to search ($Fn:$AAn, where n is the same row), the array of values to extract ($F$3:$AA$3, the dates of each week) and what to return when no result is found (e.g. "N/A" or "").

The formula in cell E5 will be:

=XLOOKUP("N", $F5:$AA5, $F$3:$AA$3, "N/A", 0, 1)