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:

1. 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”

1. 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

[#2]

@Y_Shah2475 

=INDEX($F$5:$J$5,LARGE(IF((F6:J6=1)*(YEAR(E6)>=$F$5:$J$5),COLUMN(A:E)),1))

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