Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Need Help with Index + Collect + Match Scenario

    ✭✭✭✭✭
    in Formulas and Functions

    Hello all -

    We are once again stuck on how to accomplish a look up to pull in data from one sheet to another. We thought we had it working, but turns out it wasn't.

    We have a Source Sheet and a Receiving Sheet.

    We need to pull in the Order No. from the Source Sheet to the Receiving Sheet, matching certain columns of data on both sheets, as some of the data can be duplicated on the source sheet.

    Example: Bring in the Order No. for ID 01523 + that has Lunch checked, we need to match the following columns on both sheets:

    ID_Helper + Lunch + Project No.

    The trick is that on both sheets the ID No. will have multiple occurrences with Lunch or Dinner checked, with the same or different Project No's.

    Its a situation of "look at all of these columns and find the ones that match up, and bring in that Order No.". We just haven't been able to figure out how to accomplish it with a formula.

    Test Sheets are linked below:

    Test Sheet J (receiving): https://app.smartsheet.com/b/publish?EQBCT=4278cd51fe224246abd59671114a534e

    Test Sheet K (source): https://app.smartsheet.com/b/publish?EQBCT=cbd6a20d0522421a853849658431bc63

    Any help anyone is able to offer will be greatly appreciated. Thank you! ☺️

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭
      Answer ✓

      Good morning @Victoria_Indimar !

      In our actual Source + Receiving sheets, there are some other columns in-between the ID and Dinner columns. However, I was able to take your same idea but used this:

      =[ID_Helper]@row + " " + [Project No]@row + " " + Lunch@row + " " +Dinner@row

      and it appears to be working!

      Thanks for pointing me in the right direction!

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭

      Does it have to be a formula? I feel like you could possibly do this easier with automation.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      Hello!

      ID_Helper could be a combination of those fields you're looking for in both sheets: =JOIN([ID]@row:[Lunch]@row,"+")

      Then your Order No formula in the receiving would be: =INDEX({Source_Order No},MATCH({Receiving_ID_Helper},{Source_ID_Helper},0),1


      Is that what you were looking for? Does that work for you?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭
      Answer ✓

      Good morning @Victoria_Indimar !

      In our actual Source + Receiving sheets, there are some other columns in-between the ID and Dinner columns. However, I was able to take your same idea but used this:

      =[ID_Helper]@row + " " + [Project No]@row + " " + Lunch@row + " " +Dinner@row

      and it appears to be working!

      Thanks for pointing me in the right direction!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions