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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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)
 

actuary0123

New Member
Joined
Feb 2, 2014
Messages
38
Thank you so much for your input guys. I have multiple valid approaches now versus having none a few days ago. Much appreciated.
 

actuary0123

New Member
Joined
Feb 2, 2014
Messages
38
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,515
Members
416,920
Latest member
Riskyplan

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
Top