Index, Match, Offset, I'm lost?

[Upex]

Hi All,

Hope you're safe and well.

Struggling to get the old brain to figure this one out and wonder if there any ideas please?

Need to find a chosen date within a row range say c1:h1

then find the X largest number within that respective column , say within rows 2:10 (1st 2nd largest etc will be hard coded not user selected, 1st will be used in one location, 2nd another - so presume using large vs max is the solution there)

then return the text from column A for that same row.

Pic attached of rough example - if select 3/10/22 as the date (and looking for 1st largest) the column A return would be Test 3, column B would be A. 2nd largest would offer Test 2 and then B etc.

Need it to be formula not VBA and running O365 if it makes any odds.

Thanks All

 

[Fluff]

running O365

You might want to updtae your profile to show this.


How about

=LET(f,FILTER($C$2:$G$10,$C$1:$G$1=$B$14),INDEX($A$2:$B$10,MATCH(LARGE(f,{1;2}),f,0),{1,2}))

 

[Upex]

Thanks Fluff - will try to locate profile amend and update, can't seem to see it at first pass (must not be having a good day!)

Thanks for the formula, never seen that before but seems to be working, will have a play about and see what I can learn, thank you and many thanks for posting it for me.

By chance, if I then want to add the actual result as a third column, is that an easy append to this one or need a separate formula - index and match against the name and date or such like.

 

[Fluff]

will try to locate profile amend and update

You can click on this link Account details or click your user name at the top right of the forum.

if I then want to add the actual result as a third column

Do you have functions such as CHOOSECOLS or HSTACK?

 

[Upex]

Neither come up Fluff, but no worries, I can grab those easily enough, just didn't know if there was a very easy way to embed within your solution.
Many thanks for your spot on help, much appreciated

 

[Fluff]

It can still be done with them, but just a bit longer.

=LET(f,FILTER($C$2:$G$10,$C$1:$G$1=$B$14),m,LARGE(f,{1;2}),a,INDEX($A$2:$B$10,MATCH(LARGE(f,{1;2}),f,0),{1,2}),CHOOSE({1,2,3},a,a,m))

 

[Upex]

works like a charm, Many thanks Fluff - Second solution definitely above

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Upex]

Hey Fluff, all,

Sorry to resurrect this one, but having used it a bit, I now find it doesn't like when a result is duplicated / tied for position:

for example, looking at date 2 within above, it's showing the top three results as the highest (first occurrence) duplicated 3 times, 2-2-2 rather than the three different results which all tied for first 2-4-8

Is there anyway to stop that and display the 2-4-8?

Thanks, Upex

 

[Fluff]

Maybe

=LET(f,FILTER($C$2:$G$10,$C$1:$G$1=$B$14),m,LARGE(f,{1;2}),a,FILTER($A$2:$B$10,ISNUMBER(MATCH(f,m,0))),CHOOSE({1,2,3},a,a,m))