Caleeco
Well-known Member
- Joined
- Jan 9, 2016
- Messages
- 980
- Office Version
- 2010
- Platform
- Windows
B | C | D | E | F | G | H | I | J | |
---|---|---|---|---|---|---|---|---|---|
1 | Year-Month | Product | Date | Year-Month | Product | Date | Defects | Desired output | |
2 | 201909 | A | 15/09/2019 | 201910 | A | 17/10/2019 | 13 | 201909 | |
3 | 201907 | B | 29/07/2019 | 201907 | D | 15/07/2019 | 15 | 201906 | |
4 | 201907 | C | 28/07/2019 | 201907 | C | 02/07/2019 | 4 | 201907 | |
5 | 201907 | A | 01/07/2019 | 201906 | A | 27/06/2019 | 14 | 201806 | |
6 | 201906 | D | 05/06/2019 | 201906 | B | 18/06/2019 | 8 | 201907 | |
7 | 201906 | A | 03/06/2019 | 201905 | E | 01/05/2019 | 13 | 201905 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
Hello,
Im struggling with a data-matching problem (which I can solve using VBA) but would prefer a formula solution.
Data Setup
- Table 1 (Column B:D) is static data. Shows different products made each month (Year-Month)
- Table 2 (Column E:J) is static data aside from Column J which needs to evaluate Table 1
The Problem
For each row I need Column J to look at Table 1 and ask the following questions:
- Have I made Part X (Column B) in the same Month as the defects occured (Column H)? > If YES, return Matched YEARAP (From Table 1 or 2)
- If I have not made Part X (Column B) in the same month as the defects occurred, find the nearest matching month that product was made > return that matched Year-AP (from Table 1)
- If Part X is not found in Table 1, return Year-AP from Column F
Worked Example
Row 2 - Part A. Was not made in October (Table 1) > It is found in Table 1 > Nearest Matching month is September > Return 201909.
Is this possible? Any help is appreciated as always
Many Thanks
Caleeco