Forum Discussion

SJNSkytanking's avatar
SJNSkytanking
Copper Contributor
Feb 07, 2024
Solved

How to check one column for value, then IF another column matches another cell, return value??

Hi all!   First post here, and with the work I'll be doing over the next few months, I imagine it will be the first of many questions, which I aim to return the thanks in contributions to other peo...
excel
Formulas and Functions
office 365
  • SnowMan55's avatar
    SnowMan55
    Feb 10, 2024

    SJNSkytanking 

    See the attached workbook; read the contents of the _Info worksheet.


    The matching technique that I used involved the FILTER function, available in Excel 2019 and later versions. I wrapped that function within a LET function, available in Excel 2021 and later versions, for ease of comprehension.

     

    For cell L3 on worksheet AllRowsForTeam:

    =LET( delta, $K$1, team_for_the_row, $J3,
    time_and_team_match, FILTER( Table23[TEAM],
        (Table23[STA]-TIME(0,0,0)-delta<=L$2)
       *(Table23[STD]+TIME(0,0,0)+delta>=L$2)
       *(Table23[TEAM]=team_for_the_row) ),
    IF( COUNT(time_and_team_match) > 0, team_for_the_row, "" )
)

     

Resources