Working with data betwen two sheets and mutliple columns

Good Morning All,

I am having with an issue matchhing data between two sheets based on certain criteria.

1st sheet is a list of form responses. I have a name of requestor column. I need to complete two objectives based on this requestor.

See if their name exists in one of two columns on another sheet.
2 if their name exists provide the name of their matching supervisor.

Every formula i have tried comes back #unparsable.

Column Name on 1st Sheet - Name of Requstor:

Column Names on 2nd Sheet - Approvers - Custodians

Find more posts tagged with

Formulas

Accepted answers

All comments

John_Foster

Hi @mcaulism,

I have a couple of questions.

I assume that the 1st sheet which has the name requestor is the name you want to check. Is this a text column or a contact name?
Am I correct in assuming the columns "Approvers" and "Custodians" are the columns you wish to check for the name in?
What is the name of the sheet and column which contains the supervisor's name and is this a text field or a contact field?

Thanks,

John

mcaulism

@John_Foster

It is a text column.
Yes that is correct
The name of the sheet is Petty Cash Custodians and Approvers, Column is the 2nd column.

Once this is compete i plan to use it in an approval automation.

MVP OPS

@mcaulism this looks like a combo of IF and INDEX MATCH formulas. You will add a new column to your form responses sheet name it something like "Supervisor".

See if their name exists in one of two columns on another sheet and if it exists provide supervisor name
In Column "Supervisor" input this formula
=if(index(Custodian column on other sheet, match(Name of Requestor@row, {Custodian on other sheet column},0))=Requestor@row, index({Supervisor column on other sheet}, match(Name of Requestor@row, {Custodian on other sheet column},0)), if(index(Approvers, match(Name of Requestor@row, {Approvers on other sheet column},0))=Requestor@row, index({Supervisor column petty cash sheet}, match(Name of Requestor@row, {Approvers on other sheet column},0)),"Does not Exist"))

Hope this helps,

mcaulism

Okay i still having issues. Let me provide some more examples as none of the formulas i have been trying are working.

I have my first sheet which is tied to a form. The form response sheet is named "Petty Cash Request - Master", I have a second sheet named "Petty Cash Custodians and Approvers".

The 1st Sheet has 14 columns, Name Of Requester: is column 6. The 7th column of that sheet is where i need results from the lookup. Its titled "Is the requester a custodian or approver?".

My second sheet (my lookup table) has 4 columns the 1st two being the lookup and return criteria.

When users go to submit the form I need a lookup formula to take "Name of Requestor:" from sheet "Petty Cash Request - Master" and look in columns 1 and two to determine if they are a Custodian or Approver.

The result will populate in the 7th column of the master sheet "Is the requester a custodian or approver?"

The next piece of the formula would provide the appover if the submitter is a custodian.

No matter what If, Index, Match setup i have tried has produced a valid result. Either i get Incorrect Argument or Unparsable.

Here are screenshots of the two sheets with the data involved.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.