Lookup Assistance

[Guest]

Hi, I have a retail price in column A, a list of the States in the U.S. in
column B and the sales tax C. I have a drop-down for the States which matches
up with the tax. I want this value of the tax for the state to go into cell
B2 and multiply it by the retail value. It looks like this:

A B C
1 Retail State Tax Tax Rate
2 $100 (cell to put tax rate * retail)
3 COLUMN 1 with drop down selection
4 States listed Correlating tax rates
listed

Please help, I've been trying to do the Vlookup or Index and match and I'm
not getting it. Thanks!!!

 

[Peo Sjoblom]

Maybe it's because you haven't set it up correctly, you need to create a
table first with all the states and their respective sales taxes

AL whatever%
and so on

then in the dropdown you select a state and it pulls up the tax, is that
what you are trying to do?
Also in what cell is the dropdown (validation?)

If you have a 2 column table called MyTable and the dropdown is in C2, then

=IF(C2="","",VLOOKUP(C2,MyTable,2,0))

then whatever result is returned will be used to do the calculation so in B2
you can use

=IF(C2="",0,VLOOKUP(C2,MyTable,2,0))*price





--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[L. Howard Kittle]

Hi Tiffany,

Your format is confusing to me but here's what I did.

A2 is retail price
B2 has a drop down with the states
C2 has =vlookup(b2,j1:k50,2,0)
D2 has =a2*(c2+1)
Column J has the states
Column K has the state's tax rate

So, enter retail value in A2, then select the state from B2. C2 returns the
rate for that state and D2 does the math for total price.

HTH
Regards,
Howard

 

[Guest]

Hi Howard,
I think it's a little confusing to me too! We're close though. Those
formulas work great, the only issue is now when I select the state from the
drop-down it pulls up the right sales tax and moves to that cell in the State
list. For example, AZ is B9. I think that is messing up my reference.
I don't know!!! If there is an easier way to set this up, I'm all ears! I
don't have to use a drop-down, I just need it to work! Thanks in advance
Tiffany

 

[L. Howard Kittle]

Hi Tiffany,

It's just a matter of geting it set up. I made two set ups using Peo's
suggestion and incorporated some of his suggestions into my solution.

If you want, send me an example workbook and I will take a look at it.
(e-mail address removed)

Regards,
Howard

 

[Guest]

Hi Howard,
Thanks! I tried your email but it got sent back. Is there another one I can
try?

Tiffany

 

[L. Howard Kittle]

(e-mail address removed)

Typo

Hi Tiffany,

It's just a matter of geting it set up. I made two set ups using Peo's
suggestion and incorporated some of his suggestions into my solution.

If you want, send me an example workbook and I will take a look at it.
(e-mail address removed)

Regards,
Howard

 

[L. Howard Kittle]

A typo on the address-- try
(e-mail address removed)