Use a lookup to find 2nd and 3rd iteration

[thewheatbread]

Greetings all! Trying to workout a situation that I'm stumped on where I need a lookup style function to return the name of the column when there are 2 or 3 digits within that column are the same.
I need the different LookupReason's to reference the corresponding Large value and then recall the corresponding column name if that makes sense; where it says CauseC under LookupReason2, it should really say CauseD and where it says CauseC under LookupReason3 it should really say CauseE. There is a simple LARGE function happening in the Large# columns.

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Is this for Excel or for some other app like Google Sheets?

 

[thewheatbread]

Hi & welcome to MrExcel.
Is this for Excel or for some other app like Google Sheets?

This screenshot is from Sheets but I am using Excel.

 

[Fluff]

Ok, how about

=TAKE(SORTBY($D$1:$H$1,D2:H2,-1),,3)

 

[thewheatbread]

Ok, how about

=TAKE(SORTBY($D$1:$H$1,D2:H2,-1),,3)

I get a name error?

 

[Fluff]

Ok, you may not have the Take function yet.
How about

=INDEX(SORTBY($D$1:$H$1,D2:H2,-1),,{1,2,3})

 

[thewheatbread]

Ok, you may not have the Take function yet.
How about

=INDEX(SORTBY($D$1:$H$1,D2:H2,-1),,{1,2,3})

Yeah this one worked on my small example set but for some reason it is not working on my larger set. I'll play around with it and see what happens. It's a great jumping off point.

 

[Fluff]

Ok, any problems just post some sample data

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.

 

[thewheatbread]

Ok, you may not have the Take function yet.
How about

=INDEX(SORTBY($D$1:$H$1,D2:H2,-1),,{1,2,3})

Ah ha! I have null values that have been set as a blank and it is pulling the null value titles instead. Any thought on how to deal with that?

 

[Fluff]

How about

=LET(f,FILTER(D2:I2,D2:I2<>""),INDEX(SORTBY(FILTER($D$1:$I$1,D2:I2<>""),f,-1),,{1,2,3}))