Vlookup function which recognises many dates in one month

[Cammy]

I need a function which is like vlookup in the fact that when it reads a date
in July, it then goes and finds the July cell in a range and then goes x
number of columns across to find the number. Vlookup does this only for
specific dates but I need a more generalised version which recognises that
the date is part of one of the dates in the month.
Can anyone help?

 

[Dave Peterson]

I put my date in A1 of sheet1 and this formula in B1 (of sheet1).

I put my table of dates in A1:A999 of sheet2.
I put the stuff to bring back in column D of Sheet2.

=INDEX(Sheet2!D1999,MATCH(TEXT(A1,"yyyymm"),TEXT(Sheet2!A1:A999,"yyyymm"),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


Remember, it'll bring back stuff for the first match it finds--just like
=vlookup().