google-sheetsmatchvlookup

Three-dimensional referencing


I have a dataset that looks roughly like this:

Example dataset

I'm trying to make a simplified table that pulls from that dataset:

Desired result

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:

Syntax-highlighted formula

(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?


Solution

  • 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,Λ)))),"--"))))
    

    enter image description here