VLOOKUP or IF?

[Edie G]

I have a multiple page spreadsheet for invoicing. In our state
customers pay 6% state sales tax. Additionally, each county can ad
its own surtax of .25%, .5%, .75%, or 1%. The surtax is only charge
on the first $5,000.00 of each sale.

So I have a page that is my customer list. The list includes custome
name, address and county.

I have another page that is my county tax rate list. The list include
county name, county rate, and maximum surtax. (For example, th
maximum surtax is $50.00 in a county that charges 1% surtax.)

I have an input page where I select my customer and fill in the othe
invoice information, which fills in the actual Invoice form (anothe
page).

My last page is a data page. On that page, I have VLOOKUP statement
to get the customer name, address, and county. So far, so good.

The county name is in cell H2. The following statement is in cell B4
and should return the county tax rate from the county tax rate page.

=IF(H2="Out of State",0,IF(H2="Exempt",0,VLOOKUP(H2,CountyTaxRate,3)))

As a rule this works, but not always.

Why

 

[duane]

if your counties are not in alphabetical order you might try adding a "
false" after the 3 in your lookup

 

[Edie G]

Thanks! The "FALSE" seems to have done the trick

 

[CLR]

Hi Edie..........

Depends on how it "don't work"..........could be the typing of "Out of
State" or "Exempt" is mispelled, or could be a selection of H2 is not listed
in your lookup table........or maybe your "CountyTaxRate" range is not set
for the size of the whole table............

hth
Vaya con Dios,
Chuck, CABGx3