INDEX MATCH Question

Hi everyone,

I need a quick hand with an INDEX MATCH formula.

I have a form that has users select their store from a drop down. I have two formula columns that use this entry: Region and then Regional Sales Director (RSD). I need to pull Region and RSD from a separate sheet that I don't manage. The column layout is shown here:

I got region from a simple vlookup, but I believe I need to use INDEX MATCH to get the RSD value.

I'm just failing trying to get it to work with the built in tool tips. Can you point me in the right direction?


Thanks!

Tags:

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/01/21 Answer ✓

    Hi @Ryan Levitz

    I hope you're well and safe!

    You should be able to use a VLOOKUP for the RSD as well, but I would recommend using an INDEX/MATCH instead because it's more secure.


    Here's the structure of an INDEX/MATCH formula.

    =INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row;

    {ColumnWithTheValueToMatchAgainsTheCell}; 0))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.


    If the above doesn't work, or you need additional help, could you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)


    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.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Ryan Levitz

    Hope you are fine, could you please confirm the following to help me to understand your process:

    1- your users submit the RVP using a form.

    2- after the user submission you have 2 lookup formulas 1 for ASM/RSD and 1 for Region collecting the value for each cell using the RVP submitted as criteria for search but the ASM/RSD & Region in different sheets.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/01/21 Answer ✓

    Hi @Ryan Levitz

    I hope you're well and safe!

    You should be able to use a VLOOKUP for the RSD as well, but I would recommend using an INDEX/MATCH instead because it's more secure.


    Here's the structure of an INDEX/MATCH formula.

    =INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row;

    {ColumnWithTheValueToMatchAgainsTheCell}; 0))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.


    If the above doesn't work, or you need additional help, could you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)


    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.

  • Hi @Bassam Khalil

    The sheet shown in the screenshot is sheet that contains all of the contact information for each of our stores. That info is not inputted via a form. It is relatively static info.

    The lookup formulas use the Location column in the screenshot as the criteria for the search. That's why I'm thinking vlookup won't work to pull the RSD value (since the result column is to the left of the search column).

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

    @Ryan Levitz

    You're correct that a VLOOKUP formula wouldn't work if you don't move the column or add a so-called helper column.

    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!