Index, Match and Indirect?

[vietb]

Is it possible to place a INDIRECT within a INDEX & MATCH formula? (All the formulas are refering to cells within the same sheet).

The main thing that I want to do is to get the monthly values for a number of countries. So that when I change the the date in the cell that the INDIRECT function refers too, the INDEX & MATCH formulas will get the data for those countires. This might not be the most efficent way or doing this but I cant think of any others. Any tip?

 

[Peter_SSs]

... This might not be the most efficent way or doing this but I cant think of any others. Any tip?

Any chance of seeing a sample of your layout and data using Colo's HTML maker?
http://www.mrexcel.com/board2/viewtopic.php?t=92622

It would make it easier to answer this part of your question.

 

[hatman]

Yes, you can use th eindirect formula in a Match() and Index() formula. What I typically do in situations like this is to build a Data Validation List where each entry is ALSO a Named Range for each table, to be the argumnet for the Indirect() function.

Example:

Book1
	B	C	D	E	F
2	Vehicle
3	amc		Name	Vehicle	Color
4			tom	ford	red
5			****	pontiac	orange
6	4		harry	subaru	yellow
7			jane	amc	green
8			polly	volvo	blue
9			kelley	chevy	indigo
10			scott	ford	violet
Sheet1

Where D4:D10 is named Names, E4:E10 is named Vehicles and F4:F10 is named Colors... which are also the choices available in B2. So when the user makes a selection in B2, that is the talbe where where the lookup occurs. The same method can be used for both Idex(), Vlookup() and Hlookup(). It is also a good idea to use Conditional Formatting to indicate when the lookups and tables don't match each other, because it is always possible for the user to get things crossed and end up with bogus results.

Alternately, if the tables are small or are adjacent to each other, the Offset() function can be used in liue of the indirect function.

 

[vietb]

cheers for the tips so far. I will test it out and get back to you with the results. SOrry about not including a sample of the layout, but I'm using the computers at work and its evry restrictive on what I can install and do.

 

[vietb]

Thanks for the tip! everything is fine now.

 

[hatman]

Glad we could help!