Excel Formula --> Return column heading dependent on data in rows (& conditions)

[Y_Shah2475]

Hi there,

Kindly looking for some help on the following roadblock I’ve hit:

Context:

I work for a consulting business. Depending on rules (which I won’t go into), we determine whether a client is considered “New Business” for a given Year.

For example, in the screenshot below, you will see client A is considered “New Business” in 2019, 2021 and 2023; client B in 2020 and 2023; and so on…

What I need help with:

I’m trying to create a drag-and-drop formula in column L starting in cell L4, that allows me to match the project to the relevant year that the client is considered ‘New Business’, given the following conditions:

• If the ‘project start date’ is in the same year that the client was considered ‘New Business’, then that is the year I’m trying to return
  • Example – Row 4: Project #1 starts in Jan-19 (E4) and the client was considered ‘New Business’ in 2019, therefore the result I’m trying to return in L4 is “2019"
• If the ‘project start date’ is not in the same year that the client was considered ‘New Business’, then I’m trying to return the closest historical year to project start date in which the client was considered ‘New Business’
  • Example – Row 5: Project #2 starts in May-22 (E5), but the client was not considered ‘New Business in 2022. Therefore I want to return the closest historical year in which the client was considered ‘New Business’ - In this case it would be “2021” that I’m trying to return in cell L5

Hope the above is clear – any help would be hugely appreciated!

Thanks so much,


Y

 

[Herakles]

Hi there,

Kindly looking for some help on the following roadblock I’ve hit:

Context:

I work for a consulting business. Depending on rules (which I won’t go into), we determine whether a client is considered “New Business” for a given Year.

For example, in the screenshot below, you will see client A is considered “New Business” in 2019, 2021 and 2023; client B in 2020 and 2023; and so on…

What I need help with:

I’m trying to create a drag-and-drop formula in column L starting in cell L4, that allows me to match the project to the relevant year that the client is considered ‘New Business’, given the following conditions:

• If the ‘project start date’ is in the same year that the client was considered ‘New Business’, then that is the year I’m trying to return
  • Example – Row 4: Project #1 starts in Jan-19 (E4) and the client was considered ‘New Business’ in 2019, therefore the result I’m trying to return in L4 is “2019"
• If the ‘project start date’ is not in the same year that the client was considered ‘New Business’, then I’m trying to return the closest historical year to project start date in which the client was considered ‘New Business’
  • Example – Row 5: Project #2 starts in May-22 (E5), but the client was not considered ‘New Business in 2022. Therefore I want to return the closest historical year in which the client was considered ‘New Business’ - In this case it would be “2021” that I’m trying to return in cell L5

Hope the above is clear – any help would be hugely appreciated!

Thanks so much,


Y

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.