Lookup both horizontally and vertically to match column headers with row headers

Question

I have a sheet like this:

Date|101|102|103|104|
---------------------
1/1 | A |   |   |   |
1/2 |   | A |   |   |
1/3 |   |   |   |   |
1/4 |   |   |   | A |
1/5 |   |   | A |   |

Then on a second sheet, I would like to fill in the following question marks:

ID  | DateA
-----------
101 |  ?
102 |  ?
103 |  ?

I can identify the column on sheet 1 for each ID on sheet 2 using =match(A2,Sheet1!1:1,FALSE)

But I haven't been able to figure out how to then feed this into a second match a la =match("A",Sheet1!?results from above would go here to select the right column?,FALSE)

I have been looking at the indirect functions and address functions but it is not clear to me that they will do what I need.

Answer 1

Assuming the Date column of Sheet1 is column A, and the table with "A" letters is in columns B-Z, the following performs the lookup:

=filter(Sheet1!A$2:A, filter(Sheet5!B$2:Z, Sheet5!B$1:Z$1 = A2) = "A")

The idea is that the inner filter picks the column where the ID number of interest (in A2 of the current sheet) is in the top row. The outer filter then picks the date from column A that corresponds to letter "A" in the table.

The above assumes you have one match for each ID. If there happen to be multiple dates, there are two options:

• wrap the formula in =transpose(...), so that the results will appear as a row of dates
• wrap the formula in =array_constrain(...), so that only the first match will appear.