How to Find the Furthest Status

Erica Cole

I have 2 sheets. 1 with all my active job orders, 1 with all active job applications.

I want to create a column on the job report to represent the furthest active application

Example

I have a job called "Accountant" on the Job Order sheet. The ID# is 1234

On the application report there are five applications with a Job ID of 1234. Three are in a status of "New", one is "Interviewing" and one is "Offer".

How can I create a formula that pulls in the status of the furthest application to my job order sheet for each JobID? In this case the Account job order would have a "furthest Status" of "Offer"

I've decided the weight of each status. I'll likely need to create a column on the application sheet to convert the status to it's assigned weight using an IF statement. But don't know where to go from there.

Mike TV

@Erica Cole

Using a weighted status isn't necessary. I got it working another way.

Here's an example of sheet 1 where you collect information from:

Here's an example of sheet 2 where you compile information onto:

You only need to enter the Job ID into this 2nd sheet. It will pull in everything else.

Job Type formula:

=INDEX({Job Type}, MATCH([Job ID]@row, {Job ID}, 0))

Status formula (formula starts with the "furthest status" first and then down to the lowest):

=IFERROR(INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="Offer"), 1), IFERROR(INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="Interviewing"), 1), INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="New"), 1)))

Full Name formula (same with starting from the "furthest status" type first):

=IFERROR(INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="Offer"), 1), IFERROR(INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="Interviewing"), 1), INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="New"), 1)))

Mike TV

@Erica Cole

Using a weighted status isn't necessary. I got it working another way.

Here's an example of sheet 1 where you collect information from:

Here's an example of sheet 2 where you compile information onto:

You only need to enter the Job ID into this 2nd sheet. It will pull in everything else.

Job Type formula:

=INDEX({Job Type}, MATCH([Job ID]@row, {Job ID}, 0))

Status formula (formula starts with the "furthest status" first and then down to the lowest):

=IFERROR(INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="Offer"), 1), IFERROR(INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="Interviewing"), 1), INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="New"), 1)))

Full Name formula (same with starting from the "furthest status" type first):

=IFERROR(INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="Offer"), 1), IFERROR(INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="Interviewing"), 1), INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="New"), 1)))

Erica Cole

@Mike TV

Thank you so much this is an awesome solution :)