Vlookup and hlookup combination

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. 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:
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
ABCDE
39Description2020201920182017
40Indexation increase1.90%2.20%1.50%1.40%
41Tax bracket thresholds
42Taxable income above which the 20.5% bracket begins48,53547,63046,60545,916
43Taxable income above which the 26% bracket begins97,06995,25993,20891,831
44Taxable income above which the 29% bracket begins150,473147,667144,489142,353
45Taxable income above which the 33% bracket begins214,368210,371205,842202,800
46Amounts relating to non-refundable tax credits
47Basic personal amount12,29812,06911,80911,635
48Age amount7,6377,4947,3337,225
49Net income threshold for age amount38,50837,79036,97636,430
50Spouse or common-law partner amount (maximum)12,29812,06911,80911,635
51Spouse or common-law partner amount (maximum if eligible for the Canada caregiver amount for a dependent)14,57114,29913,99113,785
52Amount for an eligible dependant (maximum)12,29812,06911,80911,635
53Amount for an eligible dependant (maximum if eligible for the Canada caregiver amount for a dependent)14,57114,29913,99113,785
54Canada caregiver amount for children under age 182,2732,2302,1822,150
55Canada employment amount (maximum)1,2451,2221,1951,178
56Canada caregiver amount for other infirm dependants age 18 or older (maximum amount)7,2767,1406,9866,883
57Net income threshold for Canada caregiver amount17,08516,76616,40516,163
58Disability amount8,5768,4168,2358,113
59Supplement for children with disabilities (maximum)5,0034,9094,8044,733
60Threshold relating to allowable child care and attendant care expenses2,9302,8752,8142,772
61Adoption expenses (maximum per adoption)16,56316,25515,90515,670
62Medical expense tax credit (3% of net income ceiling)2,3972,3522,3022,268
63Refundable medical expense supplement
64Maximum supplement1,2721,2481,2221,203
65Minimum earnings threshold3,7143,6453,5663,514
66Family net income threshold28,16427,63927,04426,644
67Old age security repayment
68Old age security repayment threshold79,05477,58075,91074,788
69Certain board and lodging allowances paid to players on sports teams or members of recreation programs
70Income exclusion (maximum per month)373366359353
71Tradesperson’s tools deduction
72Threshold amount relating to cost of eligible tools1,2451,2221,1951,178
73Canada training credit (CTC)Footnote 1
74Minimum working income threshold10,000
75Maximum net income147,667
76Goods and services tax/harmonized sales tax credit
77Adult maximum296290284280
78Child maximum155153149147
79Single supplement155153149147
80Phase-in threshold for the single supplement9,5909,4129,2099,073
81Family net income at which credit begins to phase out38,50737,78936,97636,429
82Tax-free savings account
83Annual TFSA dollar limit Footnote 26,0006,0005,5005,500
84Lifetime capital gains exemption for qualified farm or fishing property and qualified small business corporation shares Footnote 3
85Exemption limit883,384866,912848,252835,716
86Deduction limit (since ½ of the capital gain is taxable)441,692433,456424,126417,858
87Additional exemption amount for qualified farm or fishing property116,616133,088151,748164,284
88Additional deduction amount for qualified farm or fishing property (since ½ of the capital gain is taxable)58,30866,54475,87482,142
89Canada child benefit (CCB) Footnote 4
90CCB (base benefit, child under age 6)6,7656,6396,4966,400
91CCB (base benefit, child aged 6 to 17)5,7085,6025,4815,400
92Adjusted family net income at which phase out begins31,71131,12030,45030,000
93Second phase out threshold68,70867,42665,97565,000
94Base phase out amount for one eligible child2,5902,5412,4872,450
95Base phase out amount for two eligible children4,9954,9014,7964,725
96Base phase out amount for three eligible children7,0296,8986,7506,650
97Base phase out amount for four or more eligible children8,5098,3518,1718,050
98Child disability benefit (CDB)
99Maximum benefit2,8862,8322,7712,730
100Family net income threshold for phase out68,70867,42665,97565,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!
 
As it's now a structured table you can use this formula
Code:
=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.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
If it's related to the formula, then here is fine.
 
Upvote 0
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
ABCDEFGHI
1YearYMPEAYMPEYAMPEYBECPI IncreaseContrib Rate < YMPEContrib Rate > YMPEMax Employee Contribution
2196650005000600-0.01879.2
31967500050006001.0020.01879.2
41968510050336001.0020.01881
51969520051006001.0020.01882.8
61970530052006001.0020.01884.6
71971540053006001.0020.01886.4
81972550054006001.0020.01888.2
9197356005500600See Note 30.01890
101974660059007001.0820.018106.2
111975740065337001.1040.018120.6
121976830074338001.1120.018135
131977930083339001.0820.018151.2
14197810400933310001.0750.018169.2
151979117001046711001.090.018190.8
161980131001173313001.090.018212.4
171981147001316714001.0990.018239.4
181982165001476716001.1230.018268.2
191983185001656718001.1120.018300.6
201984208001860020001.0670.018338.4
211985234002090023001.0440.018379.8
221986258002333325001.040.018419.4
231987259002503325001.0410.019444.6
241988265002606726001.0440.02478
251989277002670027001.0410.021525
261990289002770028001.0480.022574.2
271991305002903330001.0480.023632.5
281992322003053332001.0580.024696
291993334003203333001.0180.025752.5
301994344003333334001.0190.026806
311995349003423334001.0050.027850.5
321996354003490035001.0180.028893.2
331997358003536735001.0150.03969
341998369003575035001.0190.0321068.8
351999374003608035001.0090.0351186.5
362000376003662035001.0160.0391329.9
372001383003720035001.0250.0431496.4
382002391003786035001.030.0471673.2
392003399003846035001.0160.04951801.8
402004405003908035001.0320.04951831.5
412005411003978035001.0170.04951861.2
422006421004054035001.0230.04951910.7
432007437004146035001.0210.04951989.9
442008449004246035001.020.04952049.3
452009463004362035001.0250.04952118.6
462010472004484035001.0040.04952163.15
472011483004608035001.0170.04952217.6
482012501004736035001.0280.04952306.7
492013511004860035001.0180.04952356.2
502014525004984035001.0090.04952425.5
512015536005112035001.0180.04952479.95
522016549005244035001.0120.04952544.3
532017553005348035001.0140.04952564.1
542018559005444035001.0150.04952593.8
552019574005542035001.0230.0512748.9
5620200.05250
5720210.05450
5820220.0570
5920230.05950
602024plus 7%0.05950.04
612025plus 14%0.05950.04
Sheet3
 
Upvote 0
How about
=INDEX(Table1[YMPE],MATCH(YEAR(TODAY()),Table1[Year],0))
Change table name in red to match the name of your table
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top