INDEX & MATCH using multiple Columns - Formula help

I just can't wrap my head around this one. I have read a lot about INDEX and Match but I have not been able to make this one work.

Heres what I need to do

  1. When the profile and finish match on another REF sheet,(The same setup with 2 columns) it will give me a value for my Formula cell. (Just need to copy over to this sheet)



Joe Goetschel | Associate Director, Smartsheet

CrossCountry Consulting - Smartsheet Partner

Email me!

"The only real limitation of Smartsheet is the level of effort required to achieve your goal."

Best Answer

Answers


  • Hi,

    You need a unique identifier that you can match up on both sheets, like system row ID column that you can hide - then on the sheet that needs to pull the data in, you manually fill in the next unique identifier (like the row ID) and then for each column try adapting this formula:

    =IFERROR(INDEX({sheet name that will act as your range}, MATCH($[Unique ID]@row, {Unique ID}, 0), MATCH([Profile]#, {sheet that will act as your source Header Row}, 0)), "")

    The items in bold is what you need to replace with your relevant sheet/field names. In my example I made use of Sheet summary field to reference in the formula, hence the: [Profile]#

    Hope this can be of some help.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Joe Goetschel

    I hope you're well and safe!

    To add to Marcelle's excellent advice/answer.

    • I'd recommend adding a so-called helper column on each o the sheets where you can combine the columns with the values you want to compare with either a JOIN formula or by adding them together with +.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Joe Goetschel
    Joe Goetschel ✭✭✭✭✭✭

    Thanks Marcelle.

    So there is no way to match 2 columns and then pull 1 value over?

    The issues I have is I need both columns to be drop downs, When the profile matches a finish, I need it to reference a number and then pull it to this sheet so I can do other formulas with it.

    Joe Goetschel | Associate Director, Smartsheet

    CrossCountry Consulting - Smartsheet Partner

    Email me!

    "The only real limitation of Smartsheet is the level of effort required to achieve your goal."

  • Joe Goetschel
    Joe Goetschel ✭✭✭✭✭✭

    I got it to work, I ditched it all and did a VERY long IF Statment and it did what I needed it to do, Thanks!

    Joe Goetschel | Associate Director, Smartsheet

    CrossCountry Consulting - Smartsheet Partner

    Email me!

    "The only real limitation of Smartsheet is the level of effort required to achieve your goal."

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Joe Goetschel

    Excellent!

    Glad you got it working!

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!