Excel lookup in tabular view

Question

I have the below tabular view and need to extract the values that match a specific criteria.

  |  A     |  B     |  C   |  D   |  F   |  G   |  H
1 |  Name  |  Hour  |  d1  |  d2  |  d3  |  d4  |  d5
2 |  M1    |  12:00 |  0   |  12  |  0   |  12  |  0
3 |  M2    |  10:00 |  8   |  4   |  6   |  0   |  8
4 |  M3    |  14:00 |  0   |  6   |  0   |  8   |  0

In a different sheet, I need to input a specific day, let's say 4, and the formula should retrieve all Names from column A that have the values from d4 (column G) greater than 0

The formula I did tried:

Cell C6 is a date field, and we only look at the day. I have it fixed by column, but I would like to have it dynamic, so when the user changes the date, the column that we look into should change as well..

=INDEX(raw!$A$2:$A$4, MATCH(0, IF(raw!$G$2:$G$4>0,COUNTIF($B$10:$B10,raw!$A$2:$A$4),""), 0))

Does anyone have an idea? Thank you!

Answer 1

Another formula; this is all-in-one and does not require CSE.

=IFERROR(INDEX(A:A, AGGREGATE(15, 6, ROW(A$2:INDEX(A:A, MATCH("zzz", A:A)))/(INDEX(C$2:INDEX(G:G, MATCH("zzz", A:A)), 0, MATCH("d"&I$2, C$1:G$1, 0))>0), ROW(1:1))), TEXT(,))

If you have an actual date in I2 then swap out "d"&I$2 for text(I$2, "\Dd") .

This formula is designed to be dynamic. If you add more rows, the formula adjusts to suit while keeping the minimum number of rows necessary for calculation.

Answer 2

After looking over older projects, I realized it could be done like this:

=IFERROR(INDEX(raw!$A$2:$A$40, MATCH(0, IF(INDIRECT("raw!$"&$F$6&"2:"&"$"&$F$6&"50")>0,COUNTIF($B$10:$B10,raw!$A$2:$A$40),""), 0)),"")

where cell F6 is:

=SUBSTITUTE(ADDRESS(1,DAY($C$6)+5,4),"1","")