Vlookup failing when value is a formula

[Opy]

Trying to use Vlookup for some date based calculations.
I want the dates being used as the Value to be formulas so we don't have to update each date cell when reporting on a new year.

When the value is text (Date format: d-mmm-yy) it is fine, but it fails if I set the date cell to a concatenate formula.

The array index uses Date format of d-mm-yyyy and it doesn't matter if I use that format or the d-mmm-yyyy format for the value cell or the vlookup formula cell, so long as it the date is typed in and not entered via a formula.

The attachment shows the formulas in the Calculations tab.
The January data does not show due to using a formula to enter the date in B1
(=CONCATENATE("1/01/",2011))
The year will actually be in cell A2, so this is more correct for what I'm after: =CONCATENATE("1/01/",$A$2)

It doesn't seem to be coming in as text, but maybe I'm not reading it right.

[teylyn]

Hello,

instead of

=CONCATENATE("1/01/",$A$2)

use

=DATE($A$2,1,1)

Format the cell to your liking with a date format.

The date formula will return a true date, whereas the concatenate returns a text value, which the Vlookup cannot find in the source data.

cheers,

[scottylad2]

=DATEVALUE(CONCATENATE("1/01/",A2)) in B1 and in C1 and drag along use EOMONTH(B1,0)+1

As <="" span="">teylyn says, you willl then need to go in and format as you need

[Opy]

Thank you both.
I went with Teylyn's option as this will be easier to program into the macros that update the workbook.