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!




Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @SmartReviewsJake

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @SmartReviewsJake

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!