Search code examples
excelexcel-formulaformulaspreadsheet

Excel - Reading data, and combining rhe matches in different sheet

I have an excel with two sheets. The first one has all the matches i work with, and the second one has all the teams, that make the pairs. In the second sheet, i have for every team some cells, where they can choose, some generic info about the time of their game (Weekends, weekdays etc). I would like to read the data in the second sheet, and

  1. If both teams have chosen Yes to a common choice (for example weekends), to have it also in the equivalent column in the first sheet.
  2. If both teams have chosen No to a common choice (for example weekends), to have it also in the equivalent column in the first sheet.
  3. Ideally if there is a match in weekday/weekend and time, in the last column to appear for example "Weekend before 21.00"

Any help appreciated!

enter image description here

enter image description here

Solution

  • Try this:

    =IF(AND(VLOOKUP($A2,Sheet2!$A:$E,MATCH(D$1,Sheet2!$A$1:$E$1,0))="Y",VLOOKUP($B2,Sheet2!$A:$E,MATCH(D$1,Sheet2!$A$1:$E$1,0))="Y"),"Y","N")

    For the extra formula, something like this might be of help:

    =IF(D2="Y",D$1 & " ","")&IF(E2="Y",E$1 & " ","")&IF(F2="Y",F$1 & " ","")&IF(G2="Y",G$1 & " ","")

    Then again, it's just a collage of filtered header. I don't know if you were looking for something more sophisticated/specific (which would be less flexible).