Difficult input table... complexed Vlookup function needed

[ChrisNor]

Hi,

I've been playing around with VLOOPUP, MATCH, SUMPRODUCT... But I can't seem to get this one right..

See the attached Excel file..

I think it should be fairly explanatory.. I want to use the dates in SUMMARY sheet, to pull out the data in the DATA TABLE sheet. The numbers in Column A on the DATA TABLE sheet, is first date in each month, and the numbers represent the day of the month.. .

Example:
01.01.2009
1
2
3
4
5
..
30

01.02.2009
..


I hope someone can help

[Cheeky Charlie]

How about something like this?
=INDEX('Data table'!B:B,MATCH(DAY(A3),OFFSET('Data table'!A$1,MATCH(DATE(YEAR(A3),MONTH(A3),1),'Data table'!A:A,0),0,30),0))

[ChrisNor]

I don't really understand the logic in this formula... Can someone explain it quickly... Using it I get #N/A...

[DonkeyOte]

The data set you provide does not cover all the dates on your Summary sheet... where the date being searched for (1st of Month) does not exist on Data Table sheet (Col A) you will get an #NA result

Ignoring this issue I would suggest a slight alternative to CC's formula:

Summary!B2:
=INDEX('Data table'!$B$1:$B$2000,MATCH($A3-DAY($A3)+1,'Data table'!$A$1:$A$2000,0)+DAY($A3))
copied down

OFFSET is Volatile so the above would generally be deemed preferable.

[ChrisNor]

Works like a charm... thanks

[Cheeky Charlie]

That is better - I try to avoid volatile functions etc. but I didn't think I could use index to reference a "column within a column" I'm accustomed to using it to reference a column or row within a 2d range...

As usual, DO, very nice.