extract data by offset

[Suresh Badhani]

i have a data i try but not sucess if i select LWP then extract all name by data shared.

 

[p45cal]

Formula in H3 (only in H3; not copied down):
=FILTER(B2:B44,C2:C44=H3)

 

[Suresh Badhani]

Formula in H3 (only in H3; not copied down):
=FILTER(B2:B44,C2:C44=H3)

Thanks for response but filter function not work in me. Plz help by offset index match

 

[bosco_yip]

In H4, formula copied down :

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(A$2:A$100)/(C$2:C$100=$H$3),ROW(A1)),0),"")

 

[Suresh Badhani]

That's great working dear brother is this possible with offset index function if u dont mind

 

[bosco_yip]

That's great working dear brother is this possible with offset index function if u dont mind

If you want to use Offset function, try this :

=IFERROR(OFFSET(B$1,AGGREGATE(15,6,ROW(A$1:A$99)/(C$2:C$100=$H$3),ROW(A1)),0),"")

 

[Suresh Badhani]

Ist thanks a lot
If don't mind can we extract this offset index match . No aggreate functiin

 

[p45cal]

What's wrong with AGGREGATE?
Is it an Excel version thing?
Try:
=IFERROR(INDEX(B:B,SMALL(ROW(A$2:A$100)/(IF(C$2:C$100=$H$3,1,1E-99)),ROW(A1)),0),"")

 

[p45cal]

This has to be one of my most ridiculous formulae. Using only 'offset index match':
In cell H4:
=IFERROR(INDEX($B$2:$B$44,MATCH(TRUE,$C$2:$C$44=$H$3,0)),"")

In cell H5:
=IFERROR(INDEX(OFFSET($B$2:$B$44,MATCH(H4,$B$2:$B$44,0),0),MATCH(TRUE,OFFSET($C$2:$C$44,MATCH(H4,$B$2:$B$44,0),0)=$H$3,0)),"")
copied down.

It relies on the names in column B being unique.

 

[Suresh Badhani]

This has to be one of my most ridiculous formulae. Using only 'offset index match':
In cell H4:
=IFERROR(INDEX($B$2:$B$44,MATCH(TRUE,$C$2:$C$44=$H$3,0)),"")

In cell H5:
=IFERROR(INDEX(OFFSET($B$2:$B$44,MATCH(H4,$B$2:$B$44,0),0),MATCH(TRUE,OFFSET($C$2:$C$44,MATCH(H4,$B$2:$B$44,0),0)=$H$3,0)),"")
copied down.

It relies on the names in column B being unique.

So great very very thanks

 

[Suresh Badhani]

So great very very thanks

but showing blank not working