If then formulas

[TGags]

In colum a are a list of state codes, in column b are the corresponding sales
tax rates for that state.

If NY is in column c1, then I want d1 to equal the sales tax rate

Can anyone help?

 

[Gary''s Student]

=VLOOKUP(C1,A1:B51,2,FALSE)

 

[Harlan Grove]

In colum a are a list of state codes, in column b are the corresponding sales
tax rates for that state.

If NY is in column c1, then I want d1 to equal the sales tax rate

D1:
=VLOOKUP(C1,$A$1:$B$51,2,0)

(51 rows if you're including D.C.)

 

[John C]

=IF(C1="","",VLOOKUP(C1,$A$1:$B$51,2,FALSE))
How are you handling county tax rates for all the different states? (Maybe
you don't need them, but it's something to think about)

Adjust your range as needed as per Harlan's suggestion (and if you have
Puerto Rico, etc.)

 

[Chip Pearson]

You can use the VLOOKUP function. For example,

=VLOOKKUP(C1,A1:B100,2,FALSE)

will scan down column A1:A100 until it finds the value named in C1. It
then goes to the second column (B) and returns the value from that
cell. The FALSE parameter indicates that you want an exact match,
rather than a "closest to but not necessarily exact" match. If the
value in C1 is not found in A1:A100, the function returns #N/A. If you
want something else rather than #N/A for a not-found error, use a
formula like

=IF(ISERROR(VLOOKUP(C1,A1:B100,2,FALSE)),"Not
Found",VLOOKUP(C1,A1:B100,2,FALSE))

This will return the text "not found" if C1 is not found within
A1:A100.

If you are working in Excel 2007, you can use

=IFERROR(VLOOKUP(C1,A1:B100,2,FALSE),"Not Found")

The IFERROR function is new to Excel 2007, so don't use it unless all
the users of the workbook will be using Excel 2007. Otherwise, they
will get a #NAME? error.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)