Index matching formula for cross referring between sheets

Hi everyone,

I would like to get help with a formula related to index matching. I have two sheets, for the purposes of this discussion - Sheet A and B

Sheet A (only has these two columns)

Sheet B is a grid view sheet that has a Smartsheet intake form. My ask is - When the intake form is submitted, if Dermatology - Dr. Ozog and Nursing - Dr. Wallis were selected in the intake form submission, this should auto-populate 1234@abc.org and 5678@abc.org respectively in the Sponsor Content Email column on Sheet B.

The formula I have devised is clearly not working, as you can see that in the image below:

=INDEX({Sponsor dept}, MATCH({Contact Email}, {Contact Email}, 0))

Sheet B

Please provide support and advise further to help resolve the issue.

Thank you SS Community once again!😊

Find more posts tagged with

Formulas

INDEX

MATCH

Accepted answers

Courtney S.

Hi! I think you have the placement of the columns backward in the formula. Take a look at this Best Practice post:

INDEX and MATCH across two sheets: a detailed explanation — Smartsheet Community

Specifically the portion that explains the formula:

Here's how INDEX MATCH works, in a nutshell:

=INDEX([Range of data to be displayed from], MATCH([Identifier], [Range to look for Identifier], [sort option]), [optional column index])

The Identifier is simply something that you can utilize to match a cell value from one sheet to another.

For this Identifier, you should use a cell value that is always unique (otherwise, if there are duplicate values, this formula will take from the first value it can find).

Here's how the INDEX MATCH pair function works:

Use the first portion of the INDEX formula to set the range of data you want to display.
Use the MATCH in the second part of the INDEX formula to designate what row to pull the data from.
The third portion of the INDEX formula is optional. Use this to specify which column to pull the data from if the first portion of the INDEX formula covers multiple columns. For how we've set this up, you won't need to worry about that.

All comments

Courtney S.

Hi! I think you have the placement of the columns backward in the formula. Take a look at this Best Practice post:

INDEX and MATCH across two sheets: a detailed explanation — Smartsheet Community

Specifically the portion that explains the formula:

Here's how INDEX MATCH works, in a nutshell:

=INDEX([Range of data to be displayed from], MATCH([Identifier], [Range to look for Identifier], [sort option]), [optional column index])

The Identifier is simply something that you can utilize to match a cell value from one sheet to another.

For this Identifier, you should use a cell value that is always unique (otherwise, if there are duplicate values, this formula will take from the first value it can find).

Here's how the INDEX MATCH pair function works:

Use the first portion of the INDEX formula to set the range of data you want to display.
Use the MATCH in the second part of the INDEX formula to designate what row to pull the data from.
The third portion of the INDEX formula is optional. Use this to specify which column to pull the data from if the first portion of the INDEX formula covers multiple columns. For how we've set this up, you won't need to worry about that.

SiddV47

Thank you for your help and detailed article links. This was resolved.

This is the formula I ended up using - =INDEX({SourceSponsoremail}, MATCH([Sponsor Department]@row, {SourceSponsorDept}, 0))

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.