Forum Discussion
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...
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, "" ) )
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, "" )
)
I honestly cannot believe a complete stranger has gone to the lengths you have to not only solve this for me, but also recreate the workbook, get it working, and even provide a detailed explanation as to how you have achieved it! You truly are a hero.
When I saw your reply and opened the workbook my jaw genuinely dropped. It works exactly as needed.
Thank you SO much for this!!
When I saw your reply and opened the workbook my jaw genuinely dropped. It works exactly as needed.
Thank you SO much for this!!