Apply New Column Based On Dates

[SamBaha]

Hello everyone, I've been working on an excel file for a while now and am stuck so any help would be super appreciated. Basically my problem is that I have two sheets, one with 3 columns, one for names, one for what office the person is in, and one for the date, the other sheet has two columns one for names and the other with a date as well, in my actual file the second sheet has many more columns and rows but this is just a sample.

I want to add a new column to the second sheet that matches the office they are in in the first sheet which is easy enough but since some people switch offices and are in one office during certain dates and then move to a separate office for others I can't figure out how to incorporate that aspect into a formula. Another issue is that some of the names in the second sheet aren't in the first but for those I just want to leave them blank. Sorry if this explanation is long winded but I've attached the file with a third sheet showing what I want the second sheet to look like so hopefully that may help.

TLDR; Sheet1 and Sheet2 are what I have and Sheet3 is what I want Sheet2 to look like.

[#2 · Accepted solution]

@SamBaha 

=IFERROR(INDEX(Sheet1!$B$2:$B$16,LARGE(IF((B2=Sheet1!$A$2:$A$16)*(Sheet2!A2>=Sheet1!$D$2:$D$16),ROW(Sheet1!$B$2:$B$16)-1),1)),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

[#3]

@SamBaha 

=TAKE(FILTER(Tabelle1[Office],(Tabelle1[Name]=B2)*(Tabelle1[Date]<=A2),""),-1)

[#4]

I've tried copying that and it hasn't worked on my file even with ctrl+shift+enter but I can see that it works on the file you uploaded so its obviously something on my end, I'll keep fiddling to try and get it to work, but I really appreciate the help!

[#5]

@SamBaha 

Do you work with the same Date format in Sheet1 and Sheet2 in your file? In the sample it works if i use e.g. 25.05.2022 in both sheets or if i use e.g. 50522 in both sheets. Otherwise the formula can't compare the dates.

[#6]

I just fixed the dates and it worked like a charm, thanks again!