leopardhawk
Well-known Member
- Joined
- May 31, 2007
- Messages
- 611
- Office Version
- 2016
- Platform
- Windows
Hello forum friends,
I am using Excel 2016, XLOOKUP is not an option for me (I think).
I have a worksheet that is linked to a website where the actual location of the data returned 'might' change over time. The formula I am currently using is INDEX/MATCH (embedded within a CONCATENATE) which has worked fine up to this point but the data I am accessing recently moved upwards by five rows (currently in row 68) and this has created a problem for me because the data was in row 73. The formula looks like this:
To solve this, I believe a combination of VLOOKUP and HLOOKUP might work better however, I can't seem to get the syntax right and I am hoping someone can help. The data I am retrieving from the web is what you see below:
So, what I need is a formula that can look at this data and return what is currently in C68 (77,580) because the current year is 2019. On January 1, 2020, the formula would return what is currently in B68 (79,054). Both the column and the row could change over time and this is why I need a formula that can find the data I need. It is extremely unlikely that the wording that is currently in A68 (Old age security repayment threshold) will ever change.
Let me know if you need any further information. Thanks in advance to anyone who can help me take this one on!
I am using Excel 2016, XLOOKUP is not an option for me (I think).
I have a worksheet that is linked to a website where the actual location of the data returned 'might' change over time. The formula I am currently using is INDEX/MATCH (embedded within a CONCATENATE) which has worked fine up to this point but the data I am accessing recently moved upwards by five rows (currently in row 68) and this has created a problem for me because the data was in row 73. The formula looks like this:
Code:
=CONCATENATE("The " &YEAR(TODAY())&" OAS Recovery Tax minimum threshold is "&TEXT(INDEX(oas_web!B73:G73,MATCH(YEAR(TODAY()),oas_web!B39:G39,0)),"$#,##0.00")&". If your net income is below this amount, the tax will not apply to you.")
To solve this, I believe a combination of VLOOKUP and HLOOKUP might work better however, I can't seem to get the syntax right and I am hoping someone can help. The data I am retrieving from the web is what you see below:
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
39 | Description | 2020 | 2019 | 2018 | 2017 | ||
40 | Indexation increase | 1.90% | 2.20% | 1.50% | 1.40% | ||
41 | Tax bracket thresholds | ||||||
42 | Taxable income above which the 20.5% bracket begins | 48,535 | 47,630 | 46,605 | 45,916 | ||
43 | Taxable income above which the 26% bracket begins | 97,069 | 95,259 | 93,208 | 91,831 | ||
44 | Taxable income above which the 29% bracket begins | 150,473 | 147,667 | 144,489 | 142,353 | ||
45 | Taxable income above which the 33% bracket begins | 214,368 | 210,371 | 205,842 | 202,800 | ||
46 | Amounts relating to non-refundable tax credits | ||||||
47 | Basic personal amount | 12,298 | 12,069 | 11,809 | 11,635 | ||
48 | Age amount | 7,637 | 7,494 | 7,333 | 7,225 | ||
49 | Net income threshold for age amount | 38,508 | 37,790 | 36,976 | 36,430 | ||
50 | Spouse or common-law partner amount (maximum) | 12,298 | 12,069 | 11,809 | 11,635 | ||
51 | Spouse or common-law partner amount (maximum if eligible for the Canada caregiver amount for a dependent) | 14,571 | 14,299 | 13,991 | 13,785 | ||
52 | Amount for an eligible dependant (maximum) | 12,298 | 12,069 | 11,809 | 11,635 | ||
53 | Amount for an eligible dependant (maximum if eligible for the Canada caregiver amount for a dependent) | 14,571 | 14,299 | 13,991 | 13,785 | ||
54 | Canada caregiver amount for children under age 18 | 2,273 | 2,230 | 2,182 | 2,150 | ||
55 | Canada employment amount (maximum) | 1,245 | 1,222 | 1,195 | 1,178 | ||
56 | Canada caregiver amount for other infirm dependants age 18 or older (maximum amount) | 7,276 | 7,140 | 6,986 | 6,883 | ||
57 | Net income threshold for Canada caregiver amount | 17,085 | 16,766 | 16,405 | 16,163 | ||
58 | Disability amount | 8,576 | 8,416 | 8,235 | 8,113 | ||
59 | Supplement for children with disabilities (maximum) | 5,003 | 4,909 | 4,804 | 4,733 | ||
60 | Threshold relating to allowable child care and attendant care expenses | 2,930 | 2,875 | 2,814 | 2,772 | ||
61 | Adoption expenses (maximum per adoption) | 16,563 | 16,255 | 15,905 | 15,670 | ||
62 | Medical expense tax credit (3% of net income ceiling) | 2,397 | 2,352 | 2,302 | 2,268 | ||
63 | Refundable medical expense supplement | ||||||
64 | Maximum supplement | 1,272 | 1,248 | 1,222 | 1,203 | ||
65 | Minimum earnings threshold | 3,714 | 3,645 | 3,566 | 3,514 | ||
66 | Family net income threshold | 28,164 | 27,639 | 27,044 | 26,644 | ||
67 | Old age security repayment | ||||||
68 | Old age security repayment threshold | 79,054 | 77,580 | 75,910 | 74,788 | ||
69 | Certain board and lodging allowances paid to players on sports teams or members of recreation programs | ||||||
70 | Income exclusion (maximum per month) | 373 | 366 | 359 | 353 | ||
71 | Tradesperson’s tools deduction | ||||||
72 | Threshold amount relating to cost of eligible tools | 1,245 | 1,222 | 1,195 | 1,178 | ||
73 | Canada training credit (CTC)Footnote 1 | ||||||
74 | Minimum working income threshold | 10,000 | |||||
75 | Maximum net income | 147,667 | |||||
76 | Goods and services tax/harmonized sales tax credit | ||||||
77 | Adult maximum | 296 | 290 | 284 | 280 | ||
78 | Child maximum | 155 | 153 | 149 | 147 | ||
79 | Single supplement | 155 | 153 | 149 | 147 | ||
80 | Phase-in threshold for the single supplement | 9,590 | 9,412 | 9,209 | 9,073 | ||
81 | Family net income at which credit begins to phase out | 38,507 | 37,789 | 36,976 | 36,429 | ||
82 | Tax-free savings account | ||||||
83 | Annual TFSA dollar limit Footnote 2 | 6,000 | 6,000 | 5,500 | 5,500 | ||
84 | Lifetime capital gains exemption for qualified farm or fishing property and qualified small business corporation shares Footnote 3 | ||||||
85 | Exemption limit | 883,384 | 866,912 | 848,252 | 835,716 | ||
86 | Deduction limit (since ½ of the capital gain is taxable) | 441,692 | 433,456 | 424,126 | 417,858 | ||
87 | Additional exemption amount for qualified farm or fishing property | 116,616 | 133,088 | 151,748 | 164,284 | ||
88 | Additional deduction amount for qualified farm or fishing property (since ½ of the capital gain is taxable) | 58,308 | 66,544 | 75,874 | 82,142 | ||
89 | Canada child benefit (CCB) Footnote 4 | ||||||
90 | CCB (base benefit, child under age 6) | 6,765 | 6,639 | 6,496 | 6,400 | ||
91 | CCB (base benefit, child aged 6 to 17) | 5,708 | 5,602 | 5,481 | 5,400 | ||
92 | Adjusted family net income at which phase out begins | 31,711 | 31,120 | 30,450 | 30,000 | ||
93 | Second phase out threshold | 68,708 | 67,426 | 65,975 | 65,000 | ||
94 | Base phase out amount for one eligible child | 2,590 | 2,541 | 2,487 | 2,450 | ||
95 | Base phase out amount for two eligible children | 4,995 | 4,901 | 4,796 | 4,725 | ||
96 | Base phase out amount for three eligible children | 7,029 | 6,898 | 6,750 | 6,650 | ||
97 | Base phase out amount for four or more eligible children | 8,509 | 8,351 | 8,171 | 8,050 | ||
98 | Child disability benefit (CDB) | ||||||
99 | Maximum benefit | 2,886 | 2,832 | 2,771 | 2,730 | ||
100 | Family net income threshold for phase out | 68,708 | 67,426 | 65,975 | 65,000 | ||
oas_web |
So, what I need is a formula that can look at this data and return what is currently in C68 (77,580) because the current year is 2019. On January 1, 2020, the formula would return what is currently in B68 (79,054). Both the column and the row could change over time and this is why I need a formula that can find the data I need. It is extremely unlikely that the wording that is currently in A68 (Old age security repayment threshold) will ever change.
Let me know if you need any further information. Thanks in advance to anyone who can help me take this one on!