Interesting Date Lookup?!

[WhytheQ]

I've got a table a bit like this:

Sep Oct Nov
1 10 20 30
2 20 300 40
3 30 40 50
4 40 50 600

In another cell I have a date e.g 02-Oct-06.
What I need is a formula that will return a value out of the table
using just the date e.g for 02-Oct-06 it should return 300, for
04-Nov-06 it should return 600.

So the formula somehow need to extract the day of the month out of the
date and then find the corresponding row, and then use the month to
find the correct column?!

Is this possible in one formula?

Any help greatly appreciated,
Jason.

 

[Bondi]

Hi Jason,

If the cell you enter your date in is A1 and the first row of datapoint
in your table is in B2 then you can use something like:

=INDEX(B25,DAY(A1),MATCH(TEXT(A1,"mmm"),B11,0))

Regards,
Bondi

 

[WhytheQ]

Thanks Bondi: that works great.
Rgds,
J