Excel using cell color to select data from another spreadsheet

[Shotgunenator]

I have a calendar built in excel and are using conditional formating to change the color of a cell using a separate spreadsheet to determine which cells to color.

The calendar worksheet is calculating the date based upon the year and month and day

(=IF(MONTH(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1)+(ROW()-5)*7+COLUMN()-3-WEEKDAY(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1),2))=MATCH($E$3,'Month Names'!$A$1:$A$12,0),DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1)+(ROW()-5)*7+COLUMN()-3-WEEKDAY(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1),2),"")

In the second sheet I have Birthday Names and Dates each in a separate column, I am using the conditional formating to compare the dates and if they agree, then the cell is colorized.

What I cannot figure out is how to import the name associated with the date into the cell which is colorized.

[#2]

@Shotgunenator Without seeing the file it a bit of a guess how that formula should look like but you could build it as follows, where the words between <> refer to the relevant ranges that contain what's described.

=INDEX(<birthday_names>, MATCH(<calendar_date>,  <birthday_dates>, 0 )

And if you give these ranges meaningful names you can avoid including sheet names and direct/absolute references in the formulae. It will make them easier to write, read and maintain.

NOTE: This is only pick-up one name per date. If you have people sharing a birthday, consider putting their names in the same cell. Otherwise it becomes much more complicated, unless you are on MS365 or Excel 2021. Then you may the FILTER function.

[#3]

@Riny_van_Eekelen Thank You I will see if I can accomplish the matter using your suggestion.

[#4]

I was able to use this formula to replicate my desired results in another location on the workbook, but was not able to use the formula in conjunction with the original formula.

Original =IF(MONTH(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1)+(ROW()-5)*7+COLUMN()-3-WEEKDAY(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1),2))=MATCH($E$3,'Month Names'!$A$1:$A$12,0),DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1)+(ROW()-5)*7+COLUMN()-3-WEEKDAY(DATE($G$3,MATCH($E$3,'Month Names'!$A$1:$A$12,0),1),2),"")

Success with index and match =IFERROR(INDEX(Names2,MATCH(VALUE(F5),Dates2,0)),"")

Names2 and Dates2 are refrences to the columns that the data is included