Formula help please

[Clive_Rabson]

I have a sheet with a year of (365) dates in Row 1 starting in cell B1

Under that is a list types of incidents in column A

The requirement is to look through each day per incident type to find the Max number of incidents and the date of that 'peak'. I can find the maximum number in the row with a formula. But what do I need to add to give me the (Vector?) of the corresponding date that will be above in Row 1?

[#2]

@Clive_Rabson 

This will get the first match.

=XLOOKUP(Maximum,B2:NC2,$B$1:$NC$1)

[#3]

Detlef Thank you.
But that does not work for me. Could it be because I am using Excel Mac 16.78 on Mac OS Monterey 12.7.4?
Using your suggestion I get: #name?

[#4]

@Clive_Rabson 

You should update. The latest version is 16.84 from April 2024.

https://learn.microsoft.com/en-us/officeupdates/update-history-office-for-mac

[#5]

@Detlef_Lewin 

Thanks.  Yes, I know, but I don’t think I can update any further on my 2013 MacBook Pro

[#6]

@Clive_Rabson 

I understand.

The use INDEX/MATCH.

=INDEX($B$1:$NC$1,MATCH(Maximum,B2:NC2,0))

[#7]

Detlef
Thanks but again that gives the result: #NAME?

[#8]

@Clive_Rabson 

I should be clear that Maximum must be replaced with the cell reference where you calculate the max.

[#9]

Detlef
That is successful.
Grateful thanks to you.

[#10]

Please could we take this one step further?

In the same sheet I also need to find the cell with first occurrence of an incident in the year (the row). Also, I need the the last occurrence of an incident in the same row.

Thanks

@Clive_Rabson 

[#11 · Accepted solution]

@Clive_Rabson 

=TAKE(FILTER($B$1:$NC$1,B2:NC2<>""),,1)
=TAKE(FILTER($B$1:$NC$1,B2:NC2<>""),,-1)

=INDEX($B$1:$NC$1,AGGREGATE(15,6,COLUMN($B$1:$NC$1)/(B2:NC2<>""),1)-1)
=INDEX($B$1:$NC$1,AGGREGATE(14,6,COLUMN($B$1:$NC$1)/(B2:NC2<>""),1)-1)

[#12]

Detlef, thank you.
Are those four formulae alternatives?
If so what are they dependent on?

[#13]

@Clive_Rabson 

The first two need a newer version of Excel. The other two need at least Excel 2010.

[#14]

@Detlef_Lewin Hi again. Sorry to trouble you with a banal question. This week I cannot see a way of starting a new post. Can you assist please?

I have another formula question

[#15]

@Detlef_Lewin 

Hello, I have another question.

I have a simple spreadsheet with three columns

Date1   Date2   ChosenDate

The cell ChosenDate needs to show the earliest date of Date1 and Date2

I thought this would work =IF(Date1<Date2,Date1,IF(Date1=Date2,Date1,IF(AND(Date1="",Date2="","none"))))

But it doesn't work. Any clue?

[#16]

@Clive_Rabson 

https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral

Click on "Start a new discussion".