Vlookup and hlookup combination

[leopardhawk]

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:

=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!

 

[Fluff]

As it's now a structured table you can use this formula

=INDEX(Indexation_amounts_in[[2020]:[2017]],MATCH("Old age security repayment threshold",Indexation_amounts_in[Description],0),MATCH(YEAR(TODAY())&"",Indexation_amounts_in[[#Headers],[2020]:[2017]],0))

No need to rename the sheet, although you can if you want, and rather than downloading the data from time to time, just goto the "Table design" tab (the cursor must be somewhere in the table for it to appear) & then click "Refresh" and everything should update itself, including the formula.

 

[leopardhawk]

Fluff, I can't thank you enough for your time. I have a couple of questions though.

1. The table will change over time in that, next year the 'Description' row will be 2021 through 2018 and the year after that it will be 2022 through 2019. Does your formula take this into account?
2. The original formula pointed to the 'oas_web!' worksheet. Your formula does not appear to point to any particular worksheet and yet, I have embedded it within a CONCATENATE formula on a worksheet called 'cpp&oas' and it's working with no errors. How is it that it still works?

 

[Fluff]

1) Yes
2) It's looking at the name of table and as you can only have one table with a given name, it does not need the name of the sheet.

 

[leopardhawk]

1) Yes
2) It's looking at the name of table and as you can only have one table with a given name, it does not need the name of the sheet.

Thank you Fluff! I have been studying your solution in the hopes that I could use it on a few other worksheets that I have linked to the Web. I have run into some issues with the syntax and am wondering if you would mind if I asked some further questions related to this subject? I could do it here in this post or open a new one.

 

[Fluff]

You'd be better of starting a new post, as this was the first time I've ever loaded data from a Website, so I may not be able to help.

 

[leopardhawk]

You'd be better of starting a new post, as this was the first time I've ever loaded data from a Website, so I may not be able to help.

Hi Fluff, My questions are more related to the INDEX/MATCH formula that you sent me on post #11. I like your idea of loading the data into a table and have set that up successfully in the workbook. I am running into problems when I try to modify your formula to bring in different data from different tables, I am getting errors. I can open another post but I thought since this was related to the formula you already suggested, it might be easier to do it here. Let me know. Thanks!

 

[Fluff]

If it's related to the formula, then here is fine.

 

[leopardhawk]

I tried using the same logic you have in the formula you already gave me but it's not working. I need the formula to return the amount in column B based on the current year.

Book1
	A	B	C	D	E	F	G	H	I
1	Year	YMPE	AYMPE	YAMPE	YBE	CPI Increase	Contrib Rate < YMPE	Contrib Rate > YMPE	Max Employee Contribution
2	1966	5000	5000		600	-	0.018		79.2
3	1967	5000	5000		600	1.002	0.018		79.2
4	1968	5100	5033		600	1.002	0.018		81
5	1969	5200	5100		600	1.002	0.018		82.8
6	1970	5300	5200		600	1.002	0.018		84.6
7	1971	5400	5300		600	1.002	0.018		86.4
8	1972	5500	5400		600	1.002	0.018		88.2
9	1973	5600	5500		600	See Note 3	0.018		90
10	1974	6600	5900		700	1.082	0.018		106.2
11	1975	7400	6533		700	1.104	0.018		120.6
12	1976	8300	7433		800	1.112	0.018		135
13	1977	9300	8333		900	1.082	0.018		151.2
14	1978	10400	9333		1000	1.075	0.018		169.2
15	1979	11700	10467		1100	1.09	0.018		190.8
16	1980	13100	11733		1300	1.09	0.018		212.4
17	1981	14700	13167		1400	1.099	0.018		239.4
18	1982	16500	14767		1600	1.123	0.018		268.2
19	1983	18500	16567		1800	1.112	0.018		300.6
20	1984	20800	18600		2000	1.067	0.018		338.4
21	1985	23400	20900		2300	1.044	0.018		379.8
22	1986	25800	23333		2500	1.04	0.018		419.4
23	1987	25900	25033		2500	1.041	0.019		444.6
24	1988	26500	26067		2600	1.044	0.02		478
25	1989	27700	26700		2700	1.041	0.021		525
26	1990	28900	27700		2800	1.048	0.022		574.2
27	1991	30500	29033		3000	1.048	0.023		632.5
28	1992	32200	30533		3200	1.058	0.024		696
29	1993	33400	32033		3300	1.018	0.025		752.5
30	1994	34400	33333		3400	1.019	0.026		806
31	1995	34900	34233		3400	1.005	0.027		850.5
32	1996	35400	34900		3500	1.018	0.028		893.2
33	1997	35800	35367		3500	1.015	0.03		969
34	1998	36900	35750		3500	1.019	0.032		1068.8
35	1999	37400	36080		3500	1.009	0.035		1186.5
36	2000	37600	36620		3500	1.016	0.039		1329.9
37	2001	38300	37200		3500	1.025	0.043		1496.4
38	2002	39100	37860		3500	1.03	0.047		1673.2
39	2003	39900	38460		3500	1.016	0.0495		1801.8
40	2004	40500	39080		3500	1.032	0.0495		1831.5
41	2005	41100	39780		3500	1.017	0.0495		1861.2
42	2006	42100	40540		3500	1.023	0.0495		1910.7
43	2007	43700	41460		3500	1.021	0.0495		1989.9
44	2008	44900	42460		3500	1.02	0.0495		2049.3
45	2009	46300	43620		3500	1.025	0.0495		2118.6
46	2010	47200	44840		3500	1.004	0.0495		2163.15
47	2011	48300	46080		3500	1.017	0.0495		2217.6
48	2012	50100	47360		3500	1.028	0.0495		2306.7
49	2013	51100	48600		3500	1.018	0.0495		2356.2
50	2014	52500	49840		3500	1.009	0.0495		2425.5
51	2015	53600	51120		3500	1.018	0.0495		2479.95
52	2016	54900	52440		3500	1.012	0.0495		2544.3
53	2017	55300	53480		3500	1.014	0.0495		2564.1
54	2018	55900	54440		3500	1.015	0.0495		2593.8
55	2019	57400	55420		3500	1.023	0.051		2748.9
56	2020						0.0525		0
57	2021						0.0545		0
58	2022						0.057		0
59	2023						0.0595		0
60	2024			plus 7%			0.0595	0.04
61	2025			plus 14%			0.0595	0.04
Sheet3

 

[Fluff]

How about
=INDEX(Table1[YMPE],MATCH(YEAR(TODAY()),Table1[Year],0))
Change table name in red to match the name of your table

 

[leopardhawk]

Okay, that works perfectly. I'm going to try to complete the other formulas that I need on my own but I may come back at some point for your advice. Thank you so much for helping me out.