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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.