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!😊
Best Answer
-
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.
- Use the first portion of the INDEX formula to set the
Answers
-
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.
- Use the first portion of the INDEX formula to set the
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!