INDEX / MATCH Formula

[kparadise]

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]

	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).

 

[Eric W]

Try:

	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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula G2 {=IFERROR(SMALL(IFERROR(1/(1/SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,C2:F2)),""),1),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[kparadise]

Perfect!

Can you explain what the formula is doing? The thing I am having difficulty understanding is the one divided by within this equation.

 

[Eric W]

Sure! The SUMIFS finds the sum of the items in column C that match both the name in A, and the class in B. Since the date is stored as a number, we get the date. Also, we're actually giving the formula 4 classes (C2:F2), and making it an array formula, so we get back 4 sums in an array, like this {0,1/7/2018,2/5/2018,0}. Now you want the smallest date, but we can't use SMALL (or MIN) just yet, since that would just give us 0. So we use a trick. We find the reciprocal of the numbers (1/). If the number is zero, we get the #DIV/0! error. We take the reciprocal again which turns the real values back to what they were, and leaves the errors as errors. Then the innermost IFERROR turns the errors to "", leaving us with {"",1/7/2018,2/5/2018,""}. Now we can use SMALL, since it ignores non-numeric values. Incidentally, I used SMALL instead of MIN, because if there are no matches, MIN returns 0, while SMALL returns an error. Using that fact with the outermost IFERROR prevents a date of 1/0/1900 from showing up.

Glad to help!