Vlookup and hlookup combination

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,056
Office Version
365
Platform
Windows
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.
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,056
Office Version
365
Platform
Windows
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

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,056
Office Version
365
Platform
Windows
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

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,056
Office Version
365
Platform
Windows
If it's related to the formula, then here is fine.
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
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.

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,056
Office Version
365
Platform
Windows
How about
=INDEX(Table1[YMPE],MATCH(YEAR(TODAY()),Table1[Year],0))
Change table name in red to match the name of your table
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
356
Office Version
2016
Platform
Windows
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.
 

Forum statistics

Threads
1,081,680
Messages
5,360,506
Members
400,589
Latest member
Mikealphatangoc

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top