Hi, I am having trouble with INDEX and MATCH, I have an appointments "all appointments" report from a CRM that I am trying to determine last contact date. I have already created the new table and created a unique member ID list but I'm stuck on the formula for identifying most recent attended appointment. I have the following data:
<tbody>
</tbody>
I Have a new table and I have used the following formula to determine unique ID Numbers:
=IFERROR(INDEX($A$1:$A$33,MATCH(0,INDEX(COUNTIF($F$1:F2,$A$1:$A$33),),0)),"")
Where I am having trouble is using Index and Match to try and calculate the nearest "Attended" appointment to Today().
Any help will be greatly appreciated.
Mike
A | B | C | |
1 | 1234 | 01/06/2018 | Attended |
2 | 1234 | 20/05/2018 | Failed to attend |
3 | 1234 | 01/05/2018 | Attended |
4 | 3456 | 02/05/2018 | Attended |
5 | 3456 | 02/06/2018 | Attended |
6 | 3456 | 02/03/2018 | Attended |
7 | 7890 | 03/03/2018 | Failed to Attend |
8 | 7890 | 03/04/2018 | Attended |
9 | 7890 | 03/05/2018 | Failed to attend |
<tbody>
</tbody>
I Have a new table and I have used the following formula to determine unique ID Numbers:
=IFERROR(INDEX($A$1:$A$33,MATCH(0,INDEX(COUNTIF($F$1:F2,$A$1:$A$33),),0)),"")
Where I am having trouble is using Index and Match to try and calculate the nearest "Attended" appointment to Today().
Any help will be greatly appreciated.
Mike