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.

    Index Match (?) to two different sheets (unique values)

    ✭✭✭✭
    in Formulas and Functions

    Hi - I am not sure what is the correct formula to use - I have tried a few and I get errors, so would appreciate some assistance. I have been using Cell Linking to pull in the data, but due to the volume it has become cumbersome and unreliable.

    I have a master project plan with unique Site IDs in the primary column, and the site details are split between two client sheets - one for each brand; also using the same unique Site IDs in the primary columns. The client sheets are very large, so when trying to select a range, I receive an error that I have exceeded the range limits if the columns are too far apart.

    I need the right formula to look up and return the information from either sheet - like a VLOOKUP but returning the data from one of two places.

    Example: Site ID 12345 (primary column) I need the activation date to pull in from either brand A or brand B, however on the brand A sheet the activation date is column 5 and on brand B it is column 15.

    What is the best type of formula to use?

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • Overachievers
      Answer ✓

      Thanks for the screen shots!

      I suggest adding two helper columns to your master sheet, one to pull in Brand A date, and one to pull in Brand B date. Formulas are listed below:

      image.png

      Brand A Activation Date: =IFERROR(INDEX({Brand A - Scheduled Date}, MATCH([Site ID]@row, {Brand A - Site ID}, 0)), "")

      Brand B Activation Date: =IFERROR(INDEX({Brand B - Activation Date}, MATCH([Site ID]@row, {Brand B - Site ID}, 0)), "")

      Scheduled RevIQ Activation Date: =IF([Brand A Activation Date]@row <> "", [Brand A Activation Date]@row, [Brand B Activation Date]@row)

      The Brand A and Brand B columns include cross-sheet references. When you copy the formulas in, you'll need to highlight the sections in { } brackets and click "Edit Reference" to select the columns in your Brand A and Brand B sheets.

      I hope that helps!

      · Share on FacebookShare on Twitter
    • Overachievers
      Answer ✓

      That is interesting... Is there anything different about how the Site ID for that one with the 2/21/23 date is formatted on either the O source sheet or the master sheet?

      It seems like the formula isn't able to find a match for the other Site IDs on the other sheets.

      · Share on FacebookShare on Twitter

    Answers

    • Overachievers

      Hi @SMacey, Index/Match is almost always a better option than VLOOKUP, so you are on the right track.

      If you'd like help with the formula(s), feel free to post screenshots of the 3 sheets and I'd be glad to help.

      www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Thank so much @Julie Fortney - I have been messing with formulas so much today I have gone cross-eyed! I thought Index/Match was the way to go, but just can't seem to get any formula right after hours of tweaking.

      Here is my sheet, where I am trying to bring in the data - the column I am trying to populate is the Scheduled Activation Date in "salmon". The primary column, "Site ID" is what I am trying to use to match, and it is unique and will be located in one of the two brand sheets below - for this example we can use the first one of 10979:

      image.png

      Here is a shot of Brand A (where the Site ID is not found - as they are part of Brand B) - note that there are TWO columns between Site & Site Status that are behind the freeze line - I am not sure if there are others that the client may have hidden, if that matters:

      image.png

      And here is the shot of Brand B where we can see 10979 listed, and therefore I am looking for the formula to pull in the associated date (1/30/23) - again to note there are slightly different columns between Brand B and Brand A:

      image.png

      Any advice would be much appreciated! Thank you in advance :)

      · Share on FacebookShare on Twitter
    • Overachievers
      Answer ✓

      Thanks for the screen shots!

      I suggest adding two helper columns to your master sheet, one to pull in Brand A date, and one to pull in Brand B date. Formulas are listed below:

      image.png

      Brand A Activation Date: =IFERROR(INDEX({Brand A - Scheduled Date}, MATCH([Site ID]@row, {Brand A - Site ID}, 0)), "")

      Brand B Activation Date: =IFERROR(INDEX({Brand B - Activation Date}, MATCH([Site ID]@row, {Brand B - Site ID}, 0)), "")

      Scheduled RevIQ Activation Date: =IF([Brand A Activation Date]@row <> "", [Brand A Activation Date]@row, [Brand B Activation Date]@row)

      The Brand A and Brand B columns include cross-sheet references. When you copy the formulas in, you'll need to highlight the sections in { } brackets and click "Edit Reference" to select the columns in your Brand A and Brand B sheets.

      I hope that helps!

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Thank you so much @Julie Fortney!

      Great idea on the helper columns! However, the formulas are returning blank cells, and every here and there one gives an error saying #INVALID COLUMN VALUE - did I do something wrong?

      image.png


      · Share on FacebookShare on Twitter
    • Overachievers

      Are your Go Live columns and the Scheduled Date columns on your A & B sheets all set as date columns?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Hi @Julie Fortney I updated my Go Live columns to dates, so that fixed the invalid column value error, but still most of the cells are returning blanks. The A&B sheets are managed by the client, but I would assume the Scheduled Date columns are set as dates. Again, really appreciate your help!

      image.png


      · Share on FacebookShare on Twitter
    • Overachievers
      Answer ✓

      That is interesting... Is there anything different about how the Site ID for that one with the 2/21/23 date is formatted on either the O source sheet or the master sheet?

      It seems like the formula isn't able to find a match for the other Site IDs on the other sheets.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Ah ha! They were blank because they are indeed...blank. I didn't even consider the most logical reason! It works!

      Thank you SO MUCH @Julie Fortney - you are a lifesaver! And this will save me so much time moving forward as well since I won't have to set up cell linking. I really appreciate your assistance!

      · Share on FacebookShare on Twitter
    • Overachievers

      I'm glad to help! I learned how to use Index/Match through the Community, so I'm glad to pass it on!

      · 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