![]() The position number can then be used within an INDEX function to return a specific value, much like VLOOKUP or HLOOKUP. ![]() When the MATCH function finds the criteria you specify, it returns the position number within the list -otherwise it returns #N/A. The MATCH function is akin to VLOOKUP - you specify what to look for, where to look, and the type of match that you’d like. ![]() ![]() The final result will involve two rather complex formulas, but we’ll build them a step at a time.įigure 1:The VLOOKUP version of the tax calculator relies on four separate rate tables.įigure 2: The new tax calculator will rely on a single rate table. This month I’ll dig deeper, and show you how to create a single table of tax rates, as shown in Figure 2. At this point I have an efficient calculator for determining the amount due based on a taxable income input, but I’d rather not have 4 separate tables. I can choose Married-Single from the list to determine the additional tax due under that filing status. If I choose Married-Joint from the list, the VLOOKUP function will calculate the tax due based on that table. INDIRECT replaced the static look-up range originally specified in the VLOOKUP formula. Finally, I modified my VLOOKUP functions to use Excel’s INDIRECT function. After assigning a range name to each table, I used Microsoft Office Excel’s Data Validation feature to create an in-cell drop-down list comprised of these range names. I then extended the functionality by creating 4 different tables - Single, Married-Joint, Married-Single, and Head of Household, as shown in Figure 1. Last month I explained how to use the VLOOKUP function to cross-reference tax rates from a tax rate table. ( If you didn’t already do so in Part 1, click here to download the accompanying Income Tax Calculator spreadsheet.) OutputImageFile.FileNamingMethod = sofnmFixed 'Tells SnagIt to use our fixed file name as specified above OutputImageFile.Directory = strOutputPath 'Tells SnagIt that we want our output to be a file ![]() 'Tells SnagIt to grab capture from the clipboard 'Sends Ctrl-C to copy image to the clipboard 'Forces Excel to finish the process before moving on Shell strProgramPath
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |