Indexmatch (retrieving date from one smartbook to generate in main sheet) with a condition

Monika_923

Good afternoon

I would like to use the Index/match function between these two sheets but there is a condition. I was provided with the great formula (thanks to @jmyzk_cloudsmart_jp ) but now i have another request. One of the employees was transferred to a different department which means he is now under another cost center effective 07.27.25.

How can I have an Index/match formula for this particular employee starting from the effective date to be posted against its new cost center?

Ex: Marcellus Adams cost center: 808514 however effective 07/27/25 his new cost center is: 909101.

attached are the sheets for your reference.

2025 OT Tracking SAMPLE - B.xlsx

CC&Names - B.xlsx

jmyzk_cloudsmart_jp

Hi @Monika_923

First, I obtain the Transfer date using the same INDEX(MATCH()) structure as the previous one.

[Transfer date] =IFERROR(INDEX({CC&Names : Transfer Date}, MATCH([Employee name]@row , {CC&Names Range : Name}, 0)), "")

https://app.smartsheet.com/b/publish?EQBCT=1e62378b8fb34058b4ea14e76db1cbf0

Then, using the [Transfer date] helper column, I used the following logic to get the Cost Center.

• [Transfer date] is a date?
  • Yes
    • [Transfer date] is on or before today?
      • Yes -> Get a New Cost Center from the CC&Names sheet.
      • No -> Get a current Cost Center from the CC&Names sheet.
  • No -> Get a current Cost Center from the CC&Names sheet.

[Cost Center] =
IF(ISDATE([Transfer date]@row), 
  IF([Transfer date]@row  <= TODAY(), 
    JOIN(COLLECT({CC&Names Range : Next Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), ""), 
    JOIN(COLLECT({CC&Names Range : Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), "")), 
JOIN(COLLECT({CC&Names Range : Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), ""))

I used this time the JOIN(COLLECT()) functions structure to avoid IFERRORs. So, the Cost Center is now a text value, but I hope that is not a problem.

Below is the same structure formula without using the [Transfer date] helper column. I think the former is easy to maintain, and I recommend it unless you prefer not to have an additional helper column.

[Cost Center Without Helper (Number)] =

IF(ISDATE(IFERROR(INDEX({CC&Names : Transfer Date}, MATCH([Employee name]@row , {CC&Names Range : Name}, 0)), "")), IF(IFERROR(INDEX({CC&Names : Transfer Date}, MATCH([Employee name]@row , {CC&Names Range : Name}, 0)), "") <= TODAY(), JOIN(COLLECT({CC&Names Range : Next Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), ""), JOIN(COLLECT({CC&Names Range : Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), "")), JOIN(COLLECT({CC&Names Range : Cost Center}, {CC&Names Range : Name}, [Employee name]@row ), ""))

https://app.smartsheet.com/b/publish?EQBCT=e9b63c6a60e048af83b217a16a0946a2