2

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.

1 Answer 1

2

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.
1
  • Thanks so much! For whatever it is worth to someone else who might benefit from this excellent answer, Sheet1 and Sheet5 above can/should refer to the same sheet (Sheet1 for me, but could be anything).
    – kramon
    Jan 22, 2017 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.