Formatting a cell does not change its actual value, only what is
displayed. For a date, the actual content is the serial number of the
date, regardless of how it is formatted.

A simple way to do your job is to insert an auxiliary column BEFORE the
column with the dates (so now column C:C becomes the auxiliary column).
In this column enter =MONTH(D3) and copy.

Your formula now becomes: =VLOOKUP(val, C3:G20, 3, false)

HTH
Kostis Vezerides