Using IF, INDEX, MATCH

ldavies95

=IF(ISBLANK([Supervisor]@row), INDEX({Staff Database Primary Supervisor}, MATCH([Staff]@row, {_Staff Database Range 2}), 0), INDEX({Staff Database Email}, MATCH([Supervisor]@row, {_Staff Database Range 2}), 0)

I am trying to index a Supervisor email from one sheet to another by matching the Staff name, however if no staff matches I want to index the staff email by matching a submitted name to the staff name. I keep getting an #INCORRECT ARGUMENT SET

Mark.poole

@ldavies95

=IF(ISBLANK([Supervisor]@row), INDEX({Staff Database Primary Supervisor}, MATCH([Staff]@row, {_Staff Database Range 2},0)), INDEX({Staff Database Email}, MATCH([Supervisor]@row, {_Staff Database Range 2},0)))

From your original formula you missed a ) at the end. As well as a slight syntax issue with the placement of the 0 The way this current formula is set up. Is that it will only run if the Supervisor@ row is blank. Would that be the only time it will not match?

ldavies95

I need this to run when the supervisor is blank, but if the email is not found due to no Staff names matching, then I need it to run based on an inputed name and bring an email to match the inputed(supervisor) name.

Mark.poole

Ok so the other question is this. are the staff names Children rows to the Supervisor name row?

ldavies95

No, the supervisor name is input IF the staff is new and not in the Staff database yet, so the Supervisor name is manually input. if the staff is a current employee and is in the staff database we do not need to manually input the Supervisor name in the row when filling out a smartsheet form.

Mark.poole

Just to be sure you understood my question. The supervisor is not located on the reference sheet? If that is the case there is nothing in the reference sheet to match [Supervisor]@row to.

ldavies95

No, originally with an existing staff member a supervisor will be listed on the staff database, so I can match the supervisor email listed in the staff database associated with the staff name located on both sheets. However, with a new hire there is no information in the staff database yet. My idea is to, for new hires, on the form that is filling my current sheet out have a logic field where they can select yes or no if a new hire, if yes, a field appears for them to manually input the supervisor. For the formula aspect, if no match is present in the staff database, then the supervisor email will pull from the staff database by matching the supervisor name and the staff name in the staff database.

Paul Newcome

So if the staff member is found in the database, you want to pull the supervisor's email. If the staff member is not found in the database, you want to pull an email based on a match to a manually entered supervisor?

Are you abel to provide some screenshots with sample data for context?

ldavies95

That is correct

Paul Newcome

And are you getting an error message when you try @Mark.poole's suggested formula?

=IF(ISBLANK([Supervisor]@row), INDEX({Staff Database Primary Supervisor}, MATCH([Staff]@row, {_Staff Database Range 2},0)), INDEX({Staff Database Email}, MATCH([Supervisor]@row, {_Staff Database Range 2},0)))

Mark.poole

OK so it sounds like you're having two issues. First in your form you're wanting a supervisor field to show when the new hire field is yes. Then. You're wanting to populate information based on rather Supervisor has an entry "which will only happen if the person is a new hire" you want it to populate the supervisor's email, or if it is blank to pull the information from the reference sheet.

ldavies95

I checked @Mark.poole's suggestion and it is still coming back as an error.

Mark.poole

@Paul Newcome Thank you for jumping in to help out. I was trying to avoid asking for a screenshot if one was not already provided and asking if there was an error with the formula would of been my next question.

Paul Newcome

What error exactly are you getting?