Index based formula:

Collins Proctor

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

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.

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)))