pickup values from 40 different tables

[ndatta]

I have pickup values from 40 different tables based on the a particular value. For example, If my value is ABC then I have to pick the value from a the table specific to ABC, and so on. Each table have 2 columns (one date and one value for that date). These two columns are same across all 40 tables.

Then if the value is XYZ I have to pick the value from XYZ table which has 2 columns (one date and one value for that date) and so on.

Please help.

 

[Special-K99]

What do you mean by "pick up"? Are you just retrieving an individual value? Or manipulating the value in some way, e.g. as part of a calculation?
You say you have a value, e.g. ABC, that indicates what table to read from and that the table consists of a dates and values but you don't say what you're looking for in that table. Are you looking for a specific date and wanting to retrieve the value associated with it? If so, where is the date you are using as a lookup? Are you expecting a value from all 40 tables? Or something else?
Where are the 40 tables stored? In the same workbook or separate files?
Are you looking for a formula solution or VBA?

 

[ndatta]

What do you mean by "pick up"? Are you just retrieving an individual value? Or manipulating the value in some way, e.g. as part of a calculation?
You say you have a value, e.g. ABC, that indicates what table to read from and that the table consists of a dates and values but you don't say what you're looking for in that table. Are you looking for a specific date and wanting to retrieve the value associated with it? If so, where is the date you are using as a lookup? Are you expecting a value from all 40 tables? Or something else?
Where are the 40 tables stored? In the same workbook or separate files?
Are you looking for a formula solution or VBA?

Tables are for different shares, they contain date and corresponding value of share, 40 tables in total.

When someone enters a share and a date, they should be able to get the value of that share for that date extracted from the tables.

It's in a same workbook.

A formula or a Vba solution will do nothing specific.

So, again, the table contains two columns, dates and value. 40 different tables

 

[Special-K99]

So a share is in your example ABC

With ABC in A1
and a date in A2
it's just a simple VLOOKUP with an INDIRECT to find that date

=VLOOKUP(A2,INDIRECT(A1&"!A1:B1000"),2,0)

Amend as required

 

[ndatta]

I don't think I explained this properly. I have this table (below) with 3 columns D,E, F. Now I have in N7, "Input Share" and in N8, "Input Date", N9 should give the output, "Value". So, the problem I am facing is I have to match N7 with Col D and N8 with Col E and get the corresponding value in Col F in N9. Trust this clarifies.

[TABLE="width: 196"]
<tbody>[TR]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[/TR]
[TR]
[TD]Share[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Swiss[/TD]
[TD]1/1/2008[/TD]
[TD]20.1[/TD]
[/TR]
[TR]
[TD]Swiss[/TD]
[TD]1/2/2008[/TD]
[TD]25.3[/TD]
[/TR]
[TR]
[TD]Swiss[/TD]
[TD]1/3/2008[/TD]
[TD]30.5[/TD]
[/TR]
[TR]
[TD]Swiss[/TD]
[TD]1/4/2008[/TD]
[TD]35.7[/TD]
[/TR]
[TR]
[TD]NYK[/TD]
[TD]1/1/2008[/TD]
[TD]60.4[/TD]
[/TR]
[TR]
[TD]NYK[/TD]
[TD]1/2/2008[/TD]
[TD]70.8[/TD]
[/TR]
[TR]
[TD]NYK[/TD]
[TD]1/3/2008[/TD]
[TD]81.2[/TD]
[/TR]
[TR]
[TD]FT100[/TD]
[TD]1/17/2008[/TD]
[TD]45.9[/TD]
[/TR]
[TR]
[TD]FT100[/TD]
[TD]1/18/2008[/TD]
[TD]52.8[/TD]
[/TR]
[TR]
[TD]FT100[/TD]
[TD]1/19/2008[/TD]
[TD]59.7[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[Special-K99]

This works

=INDEX(F2:F11,MATCH(N7,IF(E2:E11=N8,D2:D11),0),1)
Array formula, use Ctrl-Shift-Enter