Returning all row values when other rows meet criteria?

Question

I want to return a list of all the results in Row 3 that match results in a row that are over a particular number. In this case over 21. So I am looking for a formula that basically reads if year = 2021, week = 3 and results in table > 21, then return data in row 3. Using the example data it would return "540138" and "540141" since those are the two results over 21 in Week 3.

Answer 1

If you have Excel-365 then try below formula

=FILTER($E$3:$I$3,MMULT(SEQUENCE(1,ROWS($D$4:$D$8),1,0),(C4:C8=2021)*(D4:D8=3)*(E4:I8>21)))

For older version you may try-

=INDEX($E$3:$I$3,,SMALL(IF(($C$4:$C$8=2021)*($D$4:$D$8=3)*($E$4:$I$8>21),COLUMN($E$4:$I$8)-COLUMN($D$4),""),COLUMNS($A$1:A$1)))

This is an array formula. So, you need to enter the formula with CSE means CTRL+SHIFT+ENTER.

Answer 2

You can use FILTER if you have the newest version of Excel.

Something like: =FILTER($E$3:$I$3, $E6:$I6>21)