Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then formulas
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then formulas
=VLOOKUP(C1,A1:B51,2,FALSE)
-- Gary''s Student - gsnu200814 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then formulas
TGags wrote...
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.) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then formulas
=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.) -- ** John C ** "TGags" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If then formulas
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) On Wed, 19 Nov 2008 12:09:01 -0800, TGags wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |