I am trying to map dates to a number

So if a date lands between these months in (photo2) column A then formula will assign whats in the 2nd column, and anything above these numbers will be assigned "6".

I am trying to use Vlookup but not sure how to as the dates wont be exact.

Sample dates I have in my workbook:

Mapping table:

Sample output:

CodePudding user response：

You're on the right track and with VLOOKUP and an Exact match.
You just need to convert the sample dates to end of month dates for the match to work.
Excel function for that is EOMONTH:

=VLOOKUP(EOMONTH(D2,0),$A$2:$B$13,2)

The above is as it would appear cell E2, if the tables were like this:

Dealing with any date that is 'out of range'
The above covers all cases where the Sample Dates are within in the range of the Mapping Dates.
To cater for 'Out of Range' conditions, and alternate approach is required:

=IF(ISNUMBER(MATCH(EOMONTH(D2,0),$A$1:$A$13,0)),VLOOKUP(EOMONTH(D2,0),$A$1:$B$13,2),"Out of range")

The above is as it would appear cell E2, if the tables were like this:

The above first tests that the End of Month date exists in the Mapping table date using ISNUMBER with MATCH (exact).
a) If so: It does the same as the previous approach (using VLOOKUP)
b) If not: It returns the string Out of range
You can of course change the Out of range return values to whatever you like.

Dealing with specific dates being 'out of range'
Lastly: If you want to just treat specific dates as out of range (per you 'greater than Oct 2026' comment), this will do it:

=IF(D2>DATEVALUE("31 Oct 2026"),8,VLOOKUP(EOMONTH(D2,0),$A$1:$B$13,2))

Notes
a) I'm using date format (dd mmm yyyy) to avoid any confusion or issues arising out of system local date format.
b) You can of course change the out of range return (currently 8) to whatever you like.