Good evening! In my workbook everything worked as it should until the year changed from 2021 to 2022 and then all those cells got an "NA". The correct info is in the cells the VLookup is referring to but again all I get is "NA". I've been trying now for almost a month to figure this out and as you can tell haven't solved it.

You're asking for assistance with a formula to work on data and showing us neither.

If no exact match is found, #N/A is returned
MS Excel: How to use the VLOOKUP Function (WS)

Sorry about the lack of information. I hope this helps. If needed I can upload the file as well...

calendar reference.JPG

change in year.JPG

evaluated formula.JPG

Attached Files

NJ-Capacity-2021-12-22 amm.xlsm (132.6 KB, 13 views)

Your named range 'Due_Production_Date_Data' only goes to row 192

Either:

a) Define your table on sheet Calendar as Table, and refer to it using Table formula syntax;
or
b) Define the range 'Due_Production_Date_Data' as Dynamic Range,
like:
Due_Production_Date_Data = OFFSET(Calendar!$A$1,1,,COUNT(Calendar!$A:$A),5)

And, as OFFSET is a volatile function why not make your ranges Excel tables ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I want to Thank everyone that has helped.