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)