Excel - Return column reference from row and column criteria

Question

I have a dataset looking at ratings over time:

A     B          C         D          E 
ID   Date     Rating-1   Rating-2   Rating-3
1   01/01/20               Y
2   01/05/19     Y
3   15/12/19               Y

I want to extract the rating for a particular ID at a particular date. As the rating is not specified in the data (as each is represented by a Y value), I need to reference either the column heading or the column reference. To get the relevant row, I can use a Match formula: =MATCH(1,(1=$A$1:$A$4)*(DATE(2020,01,01)=$B$1:$B$4),0) - this will give row 2.

To get the column reference for a specific row, I can use a second Match formula: =MATCH("Y",$A3:$E3,0) - this will give column 4.

Is there a way to combine the two formulas to give me the column reference for a specified row (based on ID/Date criteria) and a specified column (the column with a Y value)?

I have tried a Index Match Match formula, but this seems to require the column reference to be specified, rather than finding a column with a Y value.

Answer 1

Try to avoid INDIRECT as it's volatile! There are other options such as below:

=INDEX(A1:E1,MATCH("Y",INDEX(A1:E4,MATCH(1,INDEX((A1:A4=1)*(B1:B4=DATE(2,020,1,1)),),0),0),0))

BTW, the nested INDEX is there to avoid necessity to CSE the formula. If you have ExcelO365 this could be removed.

Answer 2

Found a solution using INDIRECT, CONCATENATE and MATCH: {=MATCH("Y",INDIRECT(CONCATENATE("$A",MATCH(1,(1=$A$1:$A$4)*(DATE(2020,01,01)=$B$1:$B$4),0),":$E",MATCH(1,(1=$A$1:$A$4)*(DATE(2020,01,01)=$B$1:$B$4),0))),0)}

Effectively, the CONCATENATE and INDIRECT parts of the formula creates a range specified by ID and Date criteria. This range takes the form of a single row in which to the MATCH function then searches for the Y value and returns the column number.