I have a dataset that looks roughly like this:
I'm trying to make a simplified table that pulls from that dataset:
Looking at just Column D in my simplified table — the goal is to return the "Monday" dates corresponding to the matched up episode numbers and the "TIMING LOCK" cells that corresponds to each one.
I have a formula that successfully returns the appropriate array of dates if the "TIMING LOCK" cells were in the same row as the numbers in the dataset's D column:
(InnerTable
, EpNumCol
, and MondayRow
are named ranges in the example dataset.)
=INDEX(IFNA(VLOOKUP($B2:$B, SPLIT(TOCOL(IF(REGEXMATCH(InnerTable, "(?i)\b"&"Timing"&"\b"), EpNumCol&"×"&MondayRow, ), 1), "×"), 2, 0)))
How can I adjust this formula so that it can still match "TIMING LOCK" to the offset episode number in the big dataset?
Here's one approach which you may adapt accordingly:
=map(B2:B,lambda(Σ,if(Σ="",,ifna(+filter(MondayRow,bycol(filter(InnerTable,scan(,{EpNumCol;""},lambda(a,c,if(istext(c),a,c)))=Σ),lambda(Λ,xmatch(D1,Λ)))),"--"))))