Index / Match / Vlookup Loop across 3 sheets

[NewExcelUser]

I've got quite a challenge on my hands and it is above my comprehension of VBA at this time.

What I am looking to do is...

Match the dates in row 1 of Sheet 1, 2, or 3 with the dates listed in row 1 of sheet 4

Once the date match is found, it would then match with the label in COLUMN A of sheet 1, 2, or 3, with the column in sheet 4 with the associated date and input the appropriate numerical figure from Sheet 4 onto sheet 1, 2, or 3.

Kind of like a Vlookup on steroids, constantly looping for each date..

Any takers?

Please see attached.

Questions are welcomed.

Many thanks for your time and effort

[DonkeyOte]

Assuming it's your intention to add more and more months to your file i would suggest the following (based on your existing setup and desired results):

a) rename each sheet in mmmyy format, ie:

Sheet1: Jun09
Sheet2: Jul09
Sheet3: Aug09

b) set Sheet4 up such that:

B1: =TEXT(A1,"mmmyyy")
apply to F1, J1 etc...

Formula in D1 thus becomes:

D1: =INDEX(INDIRECT("'"&B$1&"'!A1:AH31"),MATCH(IF($B2="",$B1,$B2),INDIRECT("'"&B$1&"'!A1:A31"),0)+($B2=""),MATCH(DAY(A$1),INDIRECT("'"&B$1&"'!A2:AH2"),0))
copy down as required

NOTE: INDIRECT is Volatile and given the volume with which they will be used in this instance performance of your file may well slow - for more info. on Volatile functions see please the link in my sig to Charles Williams' site.

[royUK]

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.


Moved to Functions Forum