Pull in the last day of the year based on text

[FredFlamingo]

Hi all,

I need help with a formula, I need it to pull in the last day of the year when it looks up to a certain string of text... e.g. if it says ABC Ltd or ABC LTD International i want it to pull in 12/31/2020 otherwise any other company name it will pull in today's date. i know there is an EOmonth Formula which pulls in the end of the month but there doesn't appear to be the equivalent for end of year. i have pasted the formula i have so far below (i have removed pathways and names of files)

=IFERROR(IF(VLOOKUP(C2,.xlsx]FILE NAME'!$A:$E,5,FALSE)=("ABC Ltd"),(EOMONTH(TODAY(),0)),IF(VLOOKUP(C2,.xlsx]FILE NAME'!$A:$E,5,FALSE)=("ABC LTD International"),(EOMONTH(TODAY(),0)),(TODAY()))),TODAY())

 

[GraH]

Perhaps the date function can help you. DATE(YEAR(TODAY()),12,31)