Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Using IF, INDEX, MATCH
=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
-
=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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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?
-
That is correct
-
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)))
-
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.
-
I checked @Mark.poole's suggestion and it is still coming back as an error.
-
@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.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
