Formula to find first and last value on row and return corresponding header column

[PolyPeter]

In my spreadsheet i have row L19:AZ19 which contain duplicate number data and empty cell where i want function to find the last occurrence number then return column Header $L$2:$AZ$2 which contain Months I have try CHATGBT to find the formula but it doesn't give me the correct ones, it give me this but it only correct when the last number is unique (not duplicate) but if the last number is duplicate it return the first occurrence which it find. =INDEX($L$2:$AZ$2,1,MATCH(LOOKUP(2,1/(L19:AZ19<>""),L19:AZ19),L19:AZ19,0))

[#2 · Accepted solution]

@PolyPeter 

=INDEX($L$2:$AZ$2,LARGE(IF((ISNUMBER($L19:$AZ19))*($L19:$AZ19<>""),COLUMN($L$1:$AZ$1)-11),1))

You can try this formula to find the last value and return the corresponding header column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

[#3]

it works.... ! thank you so much, Please share the formula for the first pay.... @OliverScheurich 

[#4]

@PolyPeter 

=INDEX($L$2:$AZ$2,SMALL(IF((ISNUMBER($L19:$AZ19))*($L19:$AZ19<>""),COLUMN($L$1:$AZ$1)-11),1))

You are welcome. You can try this formula to find the first value and return the corresponding header column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.