Forum Discussion
Excel Dates lookup
I am currently working on a small project where a user inputs hours in a simple form. The hours and the date are stored in a simple list in Columns A (hrs) B (Date). A lookup list of dates are in Column N. I have used Match() function in Column G. = MATCH($B$3:$B$40,$N$3:$N$40,1) Ok so far but when run the result converts the year too 1900. all cells are format date Type *12/01/2023.
Why is this occuring and what do I need to do to correct it so the correct date is returned.
Thanks in advance.
3 Replies
paulturner What's happening is that MATCH returns an index number (between 1 and 38), based on the formula provided. And since you format the results as dates. The date value 1 is equal to January 1, 1900 and 38 is equal to February 7, 1900.
- Thanks for your reply so MATCH is not, in this case, a good solution. I didnt want anything complicated!
paulturner Perhaps you need to go with VLOOKUP or XLOOKUP if you are on a recent Excel version.
