index / match on multips tables (or vlookup)

actuary0123

New Member
Joined
Feb 2, 2014
Messages
38
I have a few tabled on a worksheet that organize interest rates in the following order:

Country A
2013201220112010
January1.000001.020001.040001.06000
February1.010001.030001.050001.07000
March1.020001.040001.060001.08000
April1.030001.050001.070001.09000
May1.040001.060001.080001.10000
June1.050001.070001.090001.11000
July1.060001.080001.100001.12000
August1.070001.090001.110001.13000
September1.080001.100001.120001.14000
October1.090001.110001.130001.15000
November1.100001.120001.140001.16000
December1.110001.130001.150001.17000

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

What Would I Like to Do:
On a separate sheet, I want to choose the country name, year and month and pull the corresponding results. All countries are organized in the same format. So far, I Am stumped. Any suggestion?

The workbook has about 50 countries and updated on daily basis. I would like to use the workbooks as it is and use formula (not VBA) to solve the problem.

Thank you.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
The trick is about to make the table_array in the vlookup dynamic. You may do so by assigning NamedRange.
3D VLOOKUP – Perform VLOOKUP from more than one table | wmfexcel

Hope this help.

Since you have 50+ sheets, it may a bit time-consuming in assignment names to all of them. If it happens that your sheet name is the same as country name, e.g. you have "A" in B2 that represent the country, then you also have "A" as the sheet names, you may try the following:

=VLOOKUP(B2,INDIRECT(B1&"!$A$5:$C$6"),2,FALSE)
where B1 is the country name that should also be the Sheet Name; A5:C6 is the Table range that should be consistent across different worksheets.
You need to make the column_index dynamic too. You use Match to do that.
 
Upvote 0
Select the whole area, that is, including the headers, for a country, say, France. Name the selection France, using the Name Box. Run this recipe for each country.

Let A2 house a month name, B2 a year, and C1 a country.

C2:

=VLOOKUP($A2,INDIRECT(C$1),MATCH($B2,INDEX(INDIRECT(C$1),1,0),0),0)

The foregoing has two INDIRECT calls. We can reduce this to just one call for all tables have the same headers.

Define Headers using Formula | Name Manager as referring to:

={"",2013,2012,2011,2010}

Now we can modify the VLOOKUP formula as follows:

=VLOOKUP($A2,INDIRECT(C$1),MATCH($B2,Headers,0),0)
 
Upvote 0
Thank you so much for your input guys. I have multiple valid approaches now versus having none a few days ago. Much appreciated.
 
Upvote 0
Try to pick out one that is correct, robust, and the most efficient, though.


Thank you for your advice. I do aspire to become such a good user where efficiency and robustness become a concern for my spreadsheets. :) For now, I was looking a convenient and correct approach and I am grateful for the help for different board member.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
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