USING VLOOKUP FOR TAX TABLES
Excel Video: Using VLOOKUP for Tax Tables
Your taxes are soon due and don't want to pay a late filing fee? Leverage the power of Microsoft Excel to get your taxes in order.
The VLOOKUP formula has a very useful optional operator. If that operator is set to FALSE, the formula will return an error if the value that you searched for doesn't show up.
If it's set to TRUE, however, it will return the next smallest number. This is perfect for tax tables.
Here's a hypothetical tax table:
Let's say you need to get tax information for three different people.
This means you'll need to make the same calculation on three different incomes. Let's use VLOOKUP to speed up the process.
Here's the syntax we'll use:
=VLOOKUP(A12, $A$1:$B$6, 2, TRUE)
A12 is the income amount, A1:B6 is the range of cells that contains the tax rates,
The number 2 indicates that we want values from the second column returned, and TRUE tells the formula that it should round down if it doesn't find an exact match.
Here's what happens when we run it on cells that contain $37,000, $44,000, and $68,000 for income values:
As you can see, the formula returned the proper tax rate for all three.
Multiplying the tax rate by the total income is simple, and will give you the amount of tax that you owe on each amount.
It's important to remember that VLOOKUP rounds down if it doesn't find the exact value it's looking for.
Because of this, if you set up a table like the one I have here, you need to have the maximum income listed in the range.
VLOOKUP can be extremely powerful.
Is the video helpful?
Drop your comment
Join us for Advanced Excel Data Analytics Masterclass
Call us on 08062468296 to register
Watch video USING VLOOKUP FOR TAX TABLES online without registration, duration hours minute second in high quality. This video was added by user Akerele Oluwasogo (Mr Excel) 01 January 1970, don't forget to share it with your friends and acquaintances, it has been viewed on our site 1,800 once and liked it 7 people.