INDEX MATCH Question

Ryan Levitz · 06/30/21

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:

Screen Shot 2021-06-30 at 10.43.24 AM.png

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!

Andrée Starå · 07/01/21

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!

Bassam Khalil · 07/01/21

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.

Andrée Starå · 07/01/21

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!

Ryan Levitz · 07/01/21

https://community.smartsheet.com/discussion/comment/296691#Comment_296691

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å · 07/01/21

@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.

INDEX MATCH Question

Best Answer

Answers

Help Article Resources

Categories