How to amend a formula to a new criteria

[georgiboy]

I wish to amend the below formula so as only if the first six characters of the lookup string have "Agency" at the beginning are returned in a gapless list.

thanks in advance

=INDEX($A$2:$A$11, MATCH(0, IF(ISBLANK($A$2:$A$11), 1, COUNTIF(B1:$B$1, $A$2:$A$11)), 0)) + CTRL + SHIFT + ENTER

[Aussiebear]

Have you tried

=Left($A$2,6(Index($A$2:$A$11,Match(0,If(IsBlank($A$2:$A411), 1, CountIf(B1:$B1, $A$2:$A$11)), 0)),'') +Ctrl+ Shift+ Enter

[Bob Phillips]

I would use


=IFERROR(INDEX($A$2:$A$11, MATCH(0, IF((LEFT($A$2:$A$11,6)="Agency")*(NOT(ISBLANK($A$2:$A$11))),COUNTIF(B1:$B$1, $A$2:$A$11),1), 0)),"")

[georgiboy]

Thank you for your responses I will evaluate what is the fastest option.

thanks again