Using IF, INDEX, MATCH

Options

=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

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/04/24
    Options

    @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?

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • ldavies95
    ldavies95 ✭✭✭
    Options

    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
    Mark.poole ✭✭✭✭✭✭
    Options

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • ldavies95
    ldavies95 ✭✭✭
    Options

    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
    Mark.poole ✭✭✭✭✭✭
    Options

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • ldavies95
    ldavies95 ✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    ldavies95 ✭✭✭
    Options

    That is correct

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Mark.poole ✭✭✭✭✭✭
    Options

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • ldavies95
    ldavies95 ✭✭✭
    Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!