Forum Discussion
Table Lookup
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]])/26
The 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! 😉