Forum Discussion
Table Lookup
Hello everyone,
I am trying to create a tax table chart that takes a typical chart of multiple rows and columns into a single row with years added and reference the data in another table to figure out federal taxes.
Below is the information for the taxes I am trying to calculate.
Here is the formula I have in the column with all the #NA:
=IF([@[Fed Taxable Income]]>0,((([@[Fed Taxable Income]]-INDEX(Tax,MATCH(YEAR([@[Period Ending]]),Tax,0),MATCH([@[Fed Taxable Income]],MATCH(YEAR([@[Period Ending]]),Tax,0),1)))*INDEX(Tax,MATCH(YEAR([@[Period Ending]]),Tax,0),MATCH([@[Fed Taxable Income]],MATCH(YEAR([@[Period Ending]]),Tax,0),1)-6))+INDEX(Tax,MATCH(YEAR([@[Period Ending]]),Tax,0),MATCH([@[Fed Taxable Income]],MATCH(YEAR([@[Period Ending]]),Tax,0),1)+6))/26,0)
With this formula, I am referencing the "Tax" table below. I am trying to have this formula look up the year of the pay period end then pull the corresponding Tax Cap column in the row based on the year or the pay period end, then adding or subtracting column index to complete the math functions to figure out the actual income tax for the pay period.
I have found that the error comes from the MATCH function not properly specifying a row for the column MATCH portion. When I specify the row as 2:2 for 2023 rather that the MATCH function that looks up 2023 the formula works.
When I have this same data in a table where the tax caps are in rows, I can easily use VLOOKUP to properly pull the relevant data, I wanted to format into single rows so that I can reference the data without having multiple tables for many years.
There are a number of things to go over here, so let's jump right in...
First and foremost, your "Tax" table has 7 "Tax %" columns, but only 6 "Tax Cap" and "Tax Amount" columns. Since you're attempting to return the "Tax Cap" column, then offset it by -6 in order to return the corresponding "Tax %" column, it appears that "Tax Cap 1" aligns with "Tax % 2". This leads me to believe that if the "Fed Taxable Income" for the month is less than "Tax Cap 1" (11,000), then "Tax % 1" (10%) is used, and the "Tax Amount" is 0. If my assumptions are correct, then you need to insert a new column for both "Tax Cap 1" and "Tax Amount 1" with a value of 0. For example:
Tax Cap 1 Tax Cap 2 Tax Cap 3 Tax Cap 4 Tax Cap 5 Tax Cap 6 Tax Cap 7 0 11,000 44,725 95,375 182,100 231,250 578,125 AND:
Tax Amount 1 Tax Amount 2 Tax Amount 3 Tax Amount 4 Tax Amount 5 Tax Amount 6 Tax Amount 7 0 1,100 5,147 16,290 37,104 52,832 174,238.25 When using MATCH with [match_type] set to 1 (Less than), it will return the largest value that is less than or equal to lookup_value, so the additional "Tax Cap 1" column with a value of 0 is needed to return a valid match when the "Fed Taxable Income" amount is 10,999.99 or less. Without it, MATCH will return #N/A in this case. Furthermore, the lookup range Tax[[Tax Cap 1]:[Tax Cap 7]] must be the same size as the return ranges Tax[[Tax % 1]:[Tax % 7]] and Tax[[Tax Amount 1]:[Tax Amount 7]], in order to return the appropriate value from the corresponding column.
Next, the method in which you are attempting to use MATCH across the entire "Tax" table will not work, because it will be evaluating every column from left-to-right, which includes data that is not applicable to the lookup. Furthermore, anywhere you've attempted to use MATCH(YEAR([@[Period Ending]]),Tax,0) will not work as expected because you're using the entire "Tax" table as the lookup_array. This needs to be adjusted to MATCH(YEAR([@[Period Ending]]),Tax[Year],0) in order to return the row number where YEAR([@[Period Ending]] finds a match within the Tax[Year] column only.
Since there are a lot of steps involved to return each piece of information, and some results need to be re-used multiple times, it's best to break up your "super" formula so each step is calculated and returned in its own column. For example, setup your output table as follows:
Period Ending Fed Taxable Income row_num col_num Tax Cap Tax % Tax Amount Federal Tax 3/31/2023 0 4/14/2023 36,460 Then, you can use the following formulas in each column:
// [row_num]: =MATCH(YEAR([@[Period Ending]]), Tax[Year], 0) // [col_num]: =MATCH([@[Fed Taxable Income]], INDEX(Tax[[Tax Cap 1]:[Tax Cap 7]], [@[row_num]], 0), 1) // [Tax Cap]: =INDEX(Tax[[Tax Cap 1]:[Tax Cap 7]], [@[row_num]], [@[col_num]]) // [Tax %]: =INDEX(Tax[[Tax % 1]:[Tax % 7]], [@[row_num]], [@[col_num]]) // [Tax Amount]: =INDEX(Tax[[Tax Amount 1]:[Tax Amount 7]], [@[row_num]], [@[col_num]]) // [Federal Tax]: =(([@[Fed Taxable Income]]-[@[Tax Cap]])*[@[Tax %]]+[@[Tax Amount]])/26The results should be as follows:
Period Ending Fed Taxable Income row_num col_num Tax Cap Tax % Tax Amount Federal Tax 3/31/2023 0 1 1 0 10% 0 0 4/14/2023 36,460 1 2 11,000 12% 1,100 159.82 I hope that all made sense! If any of my assumptions were off, please let me know where I went wrong. Cheers! 😉
