I think INDEX MATCH function? Help Please
Hello,
I am looking to pull in information from one sheet to another in a column formula that matches certain criteria in one spreadsheet. Below please find a more detailed write-up of what I am trying to do.
In screenshot one I am tying to create a formula that takes the "Total Committed Hours This Pay Period" from screenshot two and matches it based on the same names in both spreadsheets then populating this number into "Total Committed Hours This Pay Period" from screenshot one.
Is there a formula that could be suggested to help me here?
If there are any questions, feel free to ask. Thanks!
Best Answer
-
Try this
=INDEX({sheet 2 Total Committed Hours column}, Match(Name@row, {Sheet 2 Name column},0))
Since this is cross sheet references, you cannot just copy paste this in your sheet, you need to create the references using the blue Reference Another Sheet link in the formula window. On sheet 2, you will select the entire column as the range, then click the Insert Reference. As a good practice, before clicking that link, rename the range using the Column name of sheet 2, rather than the generically named Range # that smartsheet inserts. This will allow you (and the community) to better troubleshoot your formulas.
If you have any trouble with this, no worries, let me know and we'll take it step by step.
Kelly
Answers
-
Try this
=INDEX({sheet 2 Total Committed Hours column}, Match(Name@row, {Sheet 2 Name column},0))
Since this is cross sheet references, you cannot just copy paste this in your sheet, you need to create the references using the blue Reference Another Sheet link in the formula window. On sheet 2, you will select the entire column as the range, then click the Insert Reference. As a good practice, before clicking that link, rename the range using the Column name of sheet 2, rather than the generically named Range # that smartsheet inserts. This will allow you (and the community) to better troubleshoot your formulas.
If you have any trouble with this, no worries, let me know and we'll take it step by step.
Kelly
-
Wow, thank you this works great!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!