How can I get my formula to return a phrase instead of #INVALID VALUE

Erica Cole

I have a sheet that lists job openings. There is a column formula that assigns a "status" for each job by searching another sheet of applications. For example, if a job has 3 associates applications, and their statuses are "New" "Interviewing" and "Offer" it assigns that job a status of 'offer" because that is the furthest application. I use the formula below, and it works really well, except in cases where the requisition has not applications. It returns "#INVALID VALUE".

Is there a way to edit this formula so that if it finds no applications associated with the req# at the row, it will return the value "no applications"

=

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - Applicant Pool"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - Form Completed"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - Started"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - Under Review"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - In Process"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Accepted"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Extended"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Applicant Pool"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Extended"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Ready to Extend"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Pending"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Pending Approval"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Ready to Extend"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="HM - Interview Scheduled"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="HM - Eligible to Schedule"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="HM - Review"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="PS - Scheduled"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="PS - Eligible to Schedule"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="RS - Applicant Pool"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="AB - Incomplete"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="RM - Talent Community"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="RM - TalentPool"), 1), 

IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="AP - Incomplete"), 1), 

INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="AP - Applicant Pool"), 1))))))))))))))))))))))))

Paul Newcome

You would wrap the whole thing in one more IFERROR.

=IFERROR(.....................)))))))))))))))))))))))), "no applications")

Hollie Green

I would add If({Status},"","phrase you want to appear",put the rest of your formula here). This is assuming your Status is blank when there is no application. If there is something there then replace "" with what is there.

Jeff Reisman

@Erica Cole

Here's an idea that will eliminate that huge formula.

Create a helper sheet, call it Status Value. List each job status in ascending order, and assign a numeric value to each one in another column.

On your sheet of applications, create a hidden column called Status Level with a column formula INDEX/MATCH that pulls the numeric value for the status from your helper sheet. =INDEX({Status Value Sheet Number Column}, MATCH(Status@row), {Status Value Sheet Job Status Column}, 0))

By giving each status a numeric value that ascends according to the application timeline, you can then find the row with the highest status and level and collect its status text value. Back on your Requisition Status sheet, try replacing that super long formula with this one:

=IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status Level}, (MAX(COLLECT({Status Level}, {Req Number}, =[Req Number]@row))))), "No Applications")

Paul Newcome

You would wrap the whole thing in one more IFERROR.

=IFERROR(.....................)))))))))))))))))))))))), "no applications")