Please help matching data from one table to another

[dan.chodos]

I have two tables. On one table i have an income $ amount and a
corresponding state. In the second table I have a list of states and
their respective income break points and corresponding tax rates. For
example:

Table 1

NY - $33,000
NY - $38,000
CA - $41,000
CA - $53,000
CA - $85,000
etc.

Table 2
NY - $0 - 25,000 - 5%
NY - $25,001 - 35,000 - 6%
NY - $35,001 - 40,000 - 7%
NY - $40,001 and over - 8%
CA - $0 - 40,000 - 5%
CA - $40,001 - 45,000 - 6%
CA - $45,001 - 55,000 - 7%
CA - $55,001+ - 8%

I need to write a formula such that I can look up the appropriate tax
rate from Table 2 and bring it over to Table 1 based on which state
and which income tax bracket the income falls into.

Any ideas?

 

[T. Valko]

One way...

You have to structure your table 2 in a certain way. Like this:

Where the "-" is a cell delimiter

Table 2
NY - $0 - 5%
NY - $25,001 - 6%
NY - $35,001 - 7%
NY - $40,001 - 8%
CA - $0 - 5%
CA - $40,001 - 6%
CA - $45,001 - 7%
CA - $55,001 - 8%

Assume this table is in the range A10:C17

This data is in the range A2:B6

NY - $33,000
NY - $38,000
CA - $41,000
CA - $53,000
CA - $85,000

I'm assuming that the data is sorted as is portrayed.

Enter this formula in C2 and copy down as needed:

=INDEX(C$17:INDEX(C$10:C$17,MATCH(A2,A$10:A$17,0)),MATCH(B2,B$17:INDEX(B$10:B$17,MATCH(A2,A$10:A$17,0))))

Here's a screencap:

http://img126.imageshack.us/img126/57/lookuptaxtt6.jpg

 

[dan.chodos]

I have two tables. On one table i have an income $ amount and a
corresponding state. In the second table I have a list of states and
their respective income break points and corresponding tax rates. For
example:

Table 1

NY - $33,000
NY - $38,000
CA - $41,000
CA - $53,000
CA - $85,000
etc.

Table 2
NY - $0 - 25,000 - 5%
NY - $25,001 - 35,000 - 6%
NY - $35,001 - 40,000 - 7%
NY - $40,001 and over - 8%
CA - $0 - 40,000 - 5%
CA - $40,001 - 45,000 - 6%
CA - $45,001 - 55,000 - 7%
CA - $55,001+ - 8%

I need to write a formula such that I can look up the appropriate tax
rate from Table 2 and bring it over to Table 1 based on which state
and which income tax bracket the income falls into.

Any ideas?

Thanks for the help, i definitely appreciate it; however, I am still
having some trouble. While your example works great, when you begin
to expand the tables, some items are pulling from the wrong state
section. Below is an example. If you notice the AL result, according
to the table it should be 5%, but it is coming up as 7.4% which
references ID. Here is the code i have in the formula

=INDEX(G$261:INDEX(G$2:G$261,MATCH(A2,E$2:E$261,0)),MATCH(B2,F
$261:INDEX(F$2:F$261,MATCH(A2,E$2:E$261,0))))

Thanks again.


Table 1
AL $11,001 7.40% - formula result
NY $4,000 4.00%
NY $85,000 6.85%

Table 2
AK $- 0.00%
AK $1,000,000 15.00%
AL $- 2.00%
AL $501 4.00%
AL $3,001 5.00%
AL $1,000,000 15.00%
AZ $- 2.59%
AZ $10,001 2.88%
AZ $25,001 3.36%
AZ $50,001 4.24%
AZ $150,001 4.54%
AZ $1,000,000 15.00%
AR $- 1.00%
AR $3,601 2.50%
AR $7,201 3.50%
AR $10,801 4.50%
AR $18,001 6.00%
AR $30,101 7.00%
AR $1,000,000 15.00%
CA $- 1.00%
CA $6,829 2.00%
CA $16,187 4.00%
CA $25,546 6.00%
CA $35,462 8.00%
CA $44,816 9.30%
CA $1,000,000 15.00%
CO $- 4.63%
CO $1,000,000 15.00%
CT $- 3.00%
CT $10,001 5.00%
CT $1,000,000 15.00%
DE $2,001 2.20%
DE $5,001 3.90%
DE $10,001 4.80%
DE $20,001 5.20%
DE $25,001 5.55%
DE $60,001 5.95%
DE $1,000,000 15.00%
FL $- 0.00%
FL $1,000,000 15.00%
GA $- 1.00%
GA $751 2.00%
GA $2,251 3.00%
GA $3,751 4.00%
GA $5,251 5.00%
GA $7,001 6.00%
GA $1,000,000 15.00%
HI $- 1.40%
HI $2,401 3.20%
HI $4,801 5.50%
HI $9,601 6.40%
HI $14,401 6.80%
HI $19,201 7.20%
HI $24,001 7.60%
HI $36,001 7.90%
HI $48,001 8.25%
HI $1,000,000 15.00%
ID $- 1.60%
ID $1,199 3.60%
ID $2,397 4.10%
ID $3,595 5.10%
ID $4,794 6.10%
ID $5,992 7.10%
ID $8,987 7.40%
ID $23,964 7.80%
ID $1,000,000 15.00%
IL $- 3.00%
IL $1,000,000 15.00%
IN $- 3.40%
IN $1,000,000 15.00%
IA $- 0.36%
IA $1,344 0.72%
IA $2,687 2.43%
IA $5,373 4.50%
IA $12,088 6.12%
IA $20,146 6.48%
IA $26,861 6.80%
IA $40,291 7.92%
IA $60,436 8.98%
IA $1,000,000 15.00%
KS $- 3.50%
KS $15,001 6.25%
KS $30,001 6.45%
KS $1,000,000 15.00%
KY $- 2.00%
KY $3,001 3.00%
KY $4,001 4.00%
KY $5,001 5.00%
KY $8,001 5.80%
KY $75,001 6.00%
KY $1,000,000 15.00%
LA $- 2.00%
LA $25,001 4.00%
LA $50,001 6.00%
LA $1,000,000 15.00%
ME $- 2.00%
ME $4,751 4.50%
ME $9,451 7.00%
ME $18,951 8.50%
ME $1,000,000 15.00%
MD $- 2.00%
MD $1,001 3.00%
MD $2,001 4.00%
MD $3,001 4.75%
MD $125,001 5.25%
MD $150,001 5.50%
MD $200,001 5.75%
MD $1,000,000 15.00%
MA $- 5.30%
MA $1,000,000 15.00%
MI $- 4.35%
MI $1,000,000 15.00%
MN $- 5.35%
MN $21,311 7.05%
MN $69,991 7.85%
MN $1,000,000 15.00%
MS $- 3.00%
MS $5,001 4.00%
MS $10,001 5.00%
MS $1,000,000 15.00%
MO $- 1.50%
MO $1,001 2.00%
MO $2,001 2.50%
MO $3,001 3.00%
MO $4,001 3.50%
MO $5,001 4.00%
MO $6,001 4.50%
MO $7,001 5.00%
MO $8,001 5.50%
MO $9,001 6.00%
MO $1,000,000 15.00%
MT $- 1.00%
MT $2,500 2.00%
MT $4,400 3.00%
MT $6,600 4.00%
MT $9,000 5.00%
MT $11,600 6.00%
MT $14,900 6.90%
MT $1,000,000 15.00%
NE $- 2.56%
NE $2,401 3.57%
NE $17,501 5.12%
NE $27,001 6.84%
NE $1,000,000 15.00%
NV $- 0.00%
NV $1,000,000 15.00%
NH $- 5.00%
NH $1,000,000 15.00%
NJ $- 1.40%
NJ $20,001 1.75%
NJ $35,001 3.50%
NJ $40,001 5.53%
NJ $75,001 6.37%
NJ $500,001 8.97%
NJ $1,000,000 15.00%
NM $- 1.70%
NM $5,501 3.20%
NM $11,001 4.70%
NM $16,001 5.30%
NM $1,000,000 15.00%
NY $- 4.00%
NY $8,001 4.50%
NY $11,001 5.25%
NY $13,001 5.90%
NY $20,001 6.85%
NY $1,000,000 15.00%

 

[T. Valko]

Hmmm....

I see what you mean.

Ok, try this...

Based on the formula you posted it looks like Table 1 starts in A2:B2 and
Table 2 is in the range E2:G261.

Select cell A2 and create this named formula:
Note: it's important that you select cell A2 when creating the named
formula.

Goto the menu Insert>Name>Define
Name: Table
Refers to: (use your actual sheet name)

=OFFSET(Sheet1!$F$2,MATCH(Sheet1!$A2,Sheet1!$E$2:$E$261,0)-1,,COUNTIF(Sheet1!$E$2:$E$261,Sheet1!$A2),2)

OK

Then use this formula in C2 to get the tax rate:

=LOOKUP(B2,INDEX(Table,,1),INDEX(Table,,2))

 

[T. Valko]

Argh!

This is much easier than I made it look! Don't know why I didn't see this
before.

Ok...

Set up your table 2 like this using 4 columns:

State - From - To - Rate
NY - $0 - 25,000 - 5%
NY - $25,001 - 35,000 - 6%
NY - $35,001 - 40,000 - 7%
NY - $40,001 - 1E100 - 8%
CA - $0 - 40,000 - 5%
CA - $40,001 - 45,000 - 6%
CA - $45,001 - 55,000 - 7%
CA - $55,001 - 1E100 - 8%

In the last range for each state where you had a criteria of "number +", in
the "To" column just enter a really huge number that you know will never be
exceded. In the above sample I use 1E100 which is 1 followed by 100 zeros
(that's a really huge number!). (you don't have to type a 1 and then 100
zeros. Just type it in as 1E100. Excel will know what you mean!)

Then use this much simpler formula (with the above table in the range
F2:I9):

=SUMPRODUCT(--(F$2:F$9=A2),--(B2>=G$2:G$9),--(B2<=H$2:H$9),I$2:I$9)