vendredi 2 février 2018

IF MATCH multiple criteria

Table 1

Table 1

|    | 1 Jan 2018 | 2 Jan 2018 | 3 Jan 2018 | 4 Jan 2018 | 5 Jan 2018 |
|----|------------|------------|------------|------------|------------|
| A1 |            |            |            |            |            |
| A2 |            |            |            |            |            |
| A3 |            |            |            |            |            |
| A4 |            |            |            |            |            |
| A5 |            |            |            |            |            |
| A6 |            |            |            |            |            |

enter image description here Table 2

|----|----------|-----------|-----------|-----------|-----------|
| A1 | 3-Jan-18 | 10-Jan-18 | 17-Jan-18 | 24-Jan-18 | 31-Jan-18 |
| A2 | 3-Jan-18 | 10-Jan-18 | 17-Jan-18 | 24-Jan-18 | 31-Jan-18 |
| A3 | 3-Jan-18 | 10-Jan-18 | 17-Jan-18 | 24-Jan-18 | 31-Jan-18 |
| A4 | 3-Jan-18 | 6-Jan-18  | 10-Jan-18 | 13-Jan-18 | 17-Jan-18 |
| A5 | 3-Jan-18 | 10-Jan-18 | 17-Jan-18 | 24-Jan-18 | 31-Jan-18 |
| A6 | 3-Jan-18 | 10-Jan-18 | 17-Jan-18 | 24-Jan-18 | 31-Jan-18 |

IF MATCH

=IF(MATCH(A2,$A$27:$A$54,0) & MATCH(C1,$B$27:$S$54,0),"1","")

  • Getting #N/A error out of it
  • Trying to get apply formula onto the cells in Table 1 to lookup values in Table 2
  • If it matches, output is 1, else 0

Table & Image above to clearly illustrate & experiment out. Thanks in advance (:

Aucun commentaire:

Enregistrer un commentaire