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 peoples questions!

 

This one has been driving me up the wall for 2 days now, and I'm sure there is a solution to it, after much research, it probably comes down to me not looking at it the right way.

 

 

Currently:

I have this formula in every cell from L3 to IQ7 (ignore the TIME parts, they are for future changes!):

 

 

=IF(Table2[@TEAM]=1,(IF(AND(AW$2>=Table2[@STA]-TIME(0,0,0),AW$2<=Table2[@STD]+TIME(0,0,0)),1, "")),"")

 

 

In summary, the formula checks for number "1" in table column "TEAM" (column E), and if so, then checks the time is between table column "STA" and "STD", then returns the value 1 if true. There is then conditional formatting looking for "1" and turns green if found. 

 

Currently, this works as expected, and on row 3, the correct part is green. Same for row 6, as the formula is only checking the row the cell is on. As shown below:

 

 

 

 

 

What I need:

I need it to do this for every row between rows 3 to 6, so that any time a number 1 is entered in table column "TEAM" (column E), it returns value 1 in the relevant section of the timeline (which scrolls right, all the way up to time 23:55)

 

Additional:

I have tried research VLOOKUP, XLOOKUP, MATCH, INDEX, complex IF formulas, and cannot figure it out. I'm probably missing something, or just blind/dumb.

 

I really hope I have made this make sense, and if so, PLEASE can someone help me before I break my laptop?!

 

Thank you in advance!

 

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

     

3 Replies

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    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, "" )
)

     

    • SJNSkytanking's avatar
      SJNSkytanking
      Copper Contributor
      Feb 10, 2024
      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!!

Resources