Vlookup and hlookup combination

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
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:
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:

xl2bb.xlam
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,151
Office Version
365
Platform
Windows
How about
=INDEX(oas_web!B40:E100,MATCH(F2,oas_web!A40:A100,0),MATCH(YEAR(TODAY()),oas_web!B39:E39,0))

Where F2 is any cell with the value to search for (ie Old age security repayment threshold )
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
Havent got time to go into this atm but Im thinking

INDEX( table, MATCH(row), MATCH(column))
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
Fluff, thanks for reaching out. That works (if I change your F2 to "Old age security repayment threshold") but what if the data in row 39 is in a different row next year? How will the formula find 2021 if it is in row 42 next year? The webpage can change just as it did this year (by five rows).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,151
Office Version
365
Platform
Windows
How are you getting the data from the web?
Also is the data in a structured table?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,151
Office Version
365
Platform
Windows
With 2016 you should have "Get&Transform Data" rather than "Get External Data" which has "From Web"
Is that what you are using?
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
I have both "Get&Transform Data" and "Get External Data" on the Data tab. I used "Get External Data" and then "From Web" when I initially set up the worksheet. Is this what you are asking? If I add another worksheet to my workbook and go through the same process, when I click "From Web" I get a new window popup that is called "New Web Query".
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,151
Office Version
365
Platform
Windows
That's the one, if you put the url into the box, you should get something like
1575322913101.png


If you then select "Indexation amounts" and then load, it should put the table into A1 of the current sheet.
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
Ok, so I've done that on a new worksheet and it appears I now have a pivot table or some such but at least it is only the actual table from the web page and not everything else.

So, am I to presume that I should now use this new worksheet instead of the other worksheet (oas_web) and rename the new one to oas_web? If so, I suppose it is still possible that somewhere down the road, 2021 and beyond, that the row in question (Old age security repayment threshold) could theoretically change. Will your formula take this into account? I'm sorry if I'm being a pain in the #$@ but I don't have a huge understanding here of how all this works. Thanks for your help though, I really appreciate it.
 

Forum statistics

Threads
1,081,856
Messages
5,361,718
Members
400,650
Latest member
tcisrly

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top