PDA

View Full Version : How to amend a formula to a new criteria



georgiboy
11-28-2016, 09:32 AM
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
11-29-2016, 03:17 AM
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

xld
11-30-2016, 03:37 AM
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
12-04-2016, 03:30 AM
Thank you for your responses I will evaluate what is the fastest option.

thanks again