Index based formula:

I have a separate sheet with a 2-column Table Index. (Eg: "Reference Sheet Column A" has all 50 States. "Reference Sheet Column B" has the corresponding sales tax for each State.)

On another sheet, I have "Column A" that allows selection of a State from a dropdown list. In "Column B" I have the "base price" input. I need Column C to provide the automatic calculation of the the Total Sales Price (including sales tax).

So I think I need Column C to automatically calculate: Column B * (1 + the index table matching sales tax for the State selected in Column A).

Can someone help with that formula?

Thank you!


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Collins Proctor 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    [email protected]

    ☑️ 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"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To pull the percentage, you are going to want

    =INDEX({Table Sheet Percentage Column}, MATCH(State@row, {Table Sheet State Column}, 0))

    Then you can get your total like so...

    =[Base Price]@row + ([Base Price]@row * INDEX({Table Sheet Percentage Column}, MATCH(State@row, {Table Sheet State Column}, 0)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!