I hope to explain this correctly. I am needed help with an INDEX/MATCH formula to get the DATE field listed below on [TAB 1].
[TAB 1]
<tbody>
</tbody>
[TAB 2]
<tbody>
</tbody>
Soooo. What I am trying to ask for [Tab 1].[Column G] is, For [Column A]/[Row 2], look at all of the training courses [Columns C - Column F] and find those matching ID/training courses in [Tab 2]; and provide me with the earliest date.
For example. Ms. Z sold product AAA. In order to sell product AAA, you can either take training A1, A2, or B1. [Tab 2] holds all the training records. So we need to look for all of Ms.Z training records, then only look at Training A1, A2, or B1; and pick the earliest date (A2 - 1/7/2018).
[TAB 1]
A | B | C | D | E | F | G | |
1 | ID | Product | Training1 | Training2 | Training3 | Training4 | DATE |
2 | Mr.X | AAA | A1 | A2 | B1 | 1/5/2018 | |
3 | Mr.X | BBB | B2 | B3 | 1/2/2018 | ||
4 | Mr.Y | CCC | B2 | C1 | C4 | ||
5 | Ms.Z | AAA | A1 | A2 | B1 | 1/7/2018 |
<tbody>
</tbody>
[TAB 2]
A | B | C | |
1 | ID | Training | Date |
2 | Mr.X | A2 | 2/10/2018 |
3 | Mr.X | B2 | 2/5/2018 |
4 | Mr.X | B3 | 1/2/2018 |
5 | Mr.X | A1 | 1/5/2018 |
6 | Mr.Y | A1 | 1/5/2018 |
7 | Mr.Y | A2 | 2/5/2018 |
8 | Ms.Z | A2 | 1/7/2018 |
9 | Ms.Z | B1 | 2/5/2018 |
10 | Ms.Z | A5 | 11/20/2017 |
<tbody>
</tbody>
Soooo. What I am trying to ask for [Tab 1].[Column G] is, For [Column A]/[Row 2], look at all of the training courses [Columns C - Column F] and find those matching ID/training courses in [Tab 2]; and provide me with the earliest date.
For example. Ms. Z sold product AAA. In order to sell product AAA, you can either take training A1, A2, or B1. [Tab 2] holds all the training records. So we need to look for all of Ms.Z training records, then only look at Training A1, A2, or B1; and pick the earliest date (A2 - 1/7/2018).