FILTER instead of INDEX&SMALL

[mehmetics]

Hi everyone.

Is it possible to use FILTER or XLOOKUP or a new function instead of INDEX&SMALL combination to filter the desired result(s) with a shorter formula?

And how to get the column header of a cell (given) in a table with duplicate values?

Please see the attached sample.

Thank you.

[Glenn Kennedy]

No file attached!!

[mehmetics]

This is the sample

[mehmetics]

sorry it was a little bit difficult to attach.

[Fluff13]

How about

Please Login or Register  to view this content.

[mehmetics]

Thank you fot the quick reply.
this seems to be ok. How about the column headers?( Filled manualy in the right part)

[Fluff13]

Didn't realise you wanted them as well, just though you wanted to replace the index formula with filter.

[Glenn Kennedy]

I used this for the first part... pretty similar to Fluff's... still going in circles with Part 2.

=FILTER(GivenTable!A2:C13,MMULT(--(GivenTable!D2:H13=A2),TRANSPOSE(COLUMN(GivenTable!D2:H13)^0)))

[Fluff13]

Could Name1 occur multiple times on the same row?

[mehmetics]

on the same row NO

[Fluff13]

Ok, how about

Please Login or Register  to view this content.

[mehmetics]

I could not use it. Can you put the formula to the sample file.

[Fluff13]

Here you go

[Bo_Ry]

Please try

=LET(z,A2,y,GivenTable!A2:H13,h,GivenTable!D1:H1,i,SEQUENCE(ROWS(y)),j,SEQUENCE(,COLUMNS(y)),rc,SMALL(IF(y=z,i*100+j),SEQUENCE(COUNTIFS(y,z))),IFERROR(INDEX(y,rc/100,{1,2,3,-1}),INDEX(h,MOD(rc,100)-3)))

[mehmetics]

Thank you all very much.

[TMS]

If you are using FILTER, please update your profile.

[Fluff13]

Glad to help & thanks for the feedback.