Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in Multiple columns
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 D3:D102) • 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in Multiple columns
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...xman_come.html -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "ltong" wrote in message om... 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 D3:D102) . 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in Multiple columns
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in Multiple columns
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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Lenard" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup in Multiple columns
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 "Dick Kusleika" wrote in message ... 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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Lenard" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum multiple vlookup returns across multiple columns | Excel Worksheet Functions | |||
Vlookup in multiple columns | Excel Worksheet Functions | |||
Multiple columns using Vlookup | Excel Discussion (Misc queries) | |||
vlookup with multiple columns | Excel Discussion (Misc queries) | |||
vlookup for multiple columns | Excel Worksheet Functions |