i_am_tommo
New Member
- Joined
- Mar 22, 2016
- Messages
- 4
Hi all
I am banging my head on my desk trying to find a way to get this great function to work for me!
Basically, I have a recruitment process that I am following in excel - 10,000+ rows for every position I am recruiting for, with 20+ columns containing information about the position - including dates of actions completed in the process flow.
I am using a formula like this to display in Column A the 'last process step' completed according to the most recent date.
=INDEX($AT$6:$BJ$6,MATCH(MAX($AT7:$BJ7),$AT7:$BJ7,0)) <--- this is from my actual database.
To translate it to the example table below, it should be:
=INDEX($D$1:$H$1,MATCH(MAX($D2:$H2),$D2:$H2,0))
This formula works for the most part, EXCEPT for when I have two dates exactly the same in the row - in which case it returns the date furthest to the left... I need it to return according to the date furthest to the RIGHT (as the further right I am, the further along the recruitment process I am.
In the example below you will see a representation of this: Sales, Admin and PMO positions should be displaying CVs Sent, Advertised and CVs Sent respectively, but it is returning the column title according to the date furthest to the left.
Do you guys have any idea on a solution for this? It would absolutely make my day!
Thanks a lot!
Tom
For example:
<tbody>
</tbody>
I am banging my head on my desk trying to find a way to get this great function to work for me!
Basically, I have a recruitment process that I am following in excel - 10,000+ rows for every position I am recruiting for, with 20+ columns containing information about the position - including dates of actions completed in the process flow.
I am using a formula like this to display in Column A the 'last process step' completed according to the most recent date.
=INDEX($AT$6:$BJ$6,MATCH(MAX($AT7:$BJ7),$AT7:$BJ7,0)) <--- this is from my actual database.
To translate it to the example table below, it should be:
=INDEX($D$1:$H$1,MATCH(MAX($D2:$H2),$D2:$H2,0))
This formula works for the most part, EXCEPT for when I have two dates exactly the same in the row - in which case it returns the date furthest to the left... I need it to return according to the date furthest to the RIGHT (as the further right I am, the further along the recruitment process I am.
In the example below you will see a representation of this: Sales, Admin and PMO positions should be displaying CVs Sent, Advertised and CVs Sent respectively, but it is returning the column title according to the date furthest to the left.
Do you guys have any idea on a solution for this? It would absolutely make my day!
Thanks a lot!
Tom
For example:
| A1 | B1 | C1 | D1 | E1 | F1 | G1 | H1 |
| Last Status | Ref | Position | Received | Advertised | CVs Sent | Interview | Offer |
| Advertised | 103424 | Sales | 01.01.2017 | 03.01.2017 | 03.01.2017 | ||
| Received | 103234 | Admin | 01.01.2017 | 01.01.2017 | |||
| Received | 102456 | PMO | 02.01.2017 | 02.01.2017 | 02.01.2017 | ||
| Interview | 103240 | Data | 13.01.2017 | 27.01.2017 | 01.02.2017 | 05.02.2017 | |
| Offer | 103342 | Manager | 07.01.2017 | 08.01.2017 | 15.01.2017 | 01.02.2017 | 06.02.2017 |
<tbody>
</tbody>
