Vlookup in Multiple columns

[ltong]

Hi,

I'd like to look up a value of tax due (cell J15) which depends on 2
variables, Taxable Income (cell J13) and Filing Status (cell J14).

The value in Taxable Income is any amount to be filled up by the User
( ie only numeric include zero )

The value in Filing Status is a drop down list derived from row C2:F2

These 2 variables are to be retrieved from the following 2 tables
tabulated with values under respective headers : -

1) Taxable Income table with 2 columns
• At least (A2, Col A), value starting from 50,000 to 54,951
( range A3:A102)
• Less Than (B2, Col B), value starting from 50,050 to 55,000
( range B3:B102)

( ie with the incremental value of 50)

2) Filing Status table with 4 columns
• Single ( C2, Col C), value starting from 10,595 to 11,981
( range C3:C102)
• Married Filing Status ( D2, Col D), value starting from 8,307 to
9,693 ( range D3102)
• Married Filing Separately ( E2, Col E), value starting from 11,157
to 12,603 ( range E3:E102)
• Head of Household ( F2, Col F), value starting from 9,438 to 10,824
( range F3:F102)

( ie with the incremental value of 14)

Question, how to devise a vlookup function , Index/Match or other
excel function or excel VBA for the above when the value of 2
variables change, say what is the tax due

Scenario 1
if the Filing Status is selected as " Single ", based on Taxable
Income of $50,752

Scenario 2
if the Filing Status is selected as "Married Filing Status ", based
on Taxable Income of $50,752

Scenario 3
if the Filing Status is selected as "Married Filing Status ", based
on Taxable Income of $40,175

Scenario 4
if the Filing Status is selected as "Head of Household ", based on
Taxable Income of $0.00

Scenario 5
if the Filing Status is selected as "Married Filing Separately ",
based on Taxable Income of $56,024

Please help !

Thanks

Regards
Lenard

 

[Dick Kusleika]

Lenard

=VLOOKUP(J13,A3:F102,MATCH(J14,C2:F2,FALSE)+2)

This also may be of interest to you
http://www.dicks-blog.com/excel/2004/04/the_taxman_come.html

 

[Lenard]

Hi Dick,

Thanks for your suggestion and it works fine for the above scenarios
except Scenario 1.

It seems that the formula will completely ignore the changes made in one
variable (cell J14), particularly the Filing Status is changed to "
Single " with any Taxable Income, say 51,155 or 50,125, the result shown
will still be "#N/A"

I'd be much appreciate if you could briefly explain the formula,
particularly with " +2 " towards the end of vlookup formula.


Regards
Lenard

 

[Dick Kusleika]

Lenard

Sure

=VLOOKUP(
lookup_value = J13 = taxable income,
lookup_range = A3:F102 = whole table,
return_column = MATCH(
match_value = J14 = filing status,
match_range = C2:F2 = range showing filing status,
estimate = FALSE = get an exact match)
+2)

In C2:F2, you should have a range of filing statuses. Mine look like this

Single MFJ HOH MFS

The MATCH function will return a number based on where J14 is in that list.
If J14 = Single, then the MATCH function will return 1 because Single is the
first entry in the match_range. Since the lookup_table is A:F, I add two
the MATCH function to sync it with the whole table. For instance, if J13 =
MFJ, the MATCH function will return 2 (the second item in the list) but that
will mean that I want the 4th column of the table, so I add 2.

If you're getting N/A, it almost certainly is that the value in J14 does not
match any values in C2:F2.

If you like, I can send you my example workbook.

 

[ltong]

Hi Dick,

Thanks a lot for your prompt and great reply.

Finally I manage to solve the scenario 1 and it is due to the value in
the range C2:F2 mismatch with the variable in J14, ie the text string
too long.

Thanks again for your kind offer of your example workbook

Regards
Lenard