Error Array missing 1st occurrence

[Paul Naylor]

Trying to output results in list form for results listed in column FZ ( formula - see below) , but array output is missing the 1st occurance of each and not sure why as checked and all entrys are correct?

Array formula : =IFERROR(VLOOKUP(GC3&ROW($C$4:$C$250),$C$4:$FZ$250,180,FALSE),"")

GC3-GV3 list of names

$C$4 - $C$250 is lookup list of names , where duplicate names show as John Smith1, John Smith2, JohnSmith3...... ( but also contains blank cells)

$C$4:$FZ$250 is look up range with column FZ contains a list of values that i want to display - Formula in FZ, summing various other cells =COUNTIFS(X4:AA4,"<>Yes",X4:AA4,"<>N/A")+COUNTIFS(AC4:AG4,"<>Yes",AC4:AG4,"<>N/A")+COUNTIFS(AI4:AL4,"<>Yes",AI4:AL4,"<>N/A")+COUNTIFS(AN4:AR4,"<>Yes",AN4:AR4,"<>N/A")+COUNTIFS(AT4:AY4,"<>Yes",AT4:AY4,"<>N/A")+COUNTIFS(BA4,"<>Yes",BA4,"<>N/A")) contains numbers or blank cells no errors !

Error rate array formula missing the 1st occurrence of each of the names listed in GC3-GV3 anyone any ideas why ?

 

[StephenCrump]

If GC3 is "John Smith", then GC3&ROW($C$4:$C$250) will be JohnSmith4, JohnSmith5, John Smith6 ....

i.e. you will be missing the first 4 occurrences: John Smith, John Smith1, John Smith2 and John Smith3.

Will this work for you?

=FILTER(FZ$4:FZ$250,ISNUMBER(MATCH(C$4:C$250,GC3&IFERROR(1/(1/SEQUENCE(250,,0)),""),)))

 

[Paul Naylor]

If GC3 is "John Smith", then GC3&ROW($C$4:$C$250) will be JohnSmith4, JohnSmith5, John Smith6 ....

i.e. you will be missing the first 4 occurrences: John Smith, John Smith1, John Smith2 and John Smith3.

Will this work for you?

=FILTER(FZ$4:FZ$250,ISNUMBER(MATCH(C$4:C$250,GC3&IFERROR(1/(1/SEQUENCE(250,,0)),""),)))

Hi Stephen, Works a treat thankyou so much

 

[StephenCrump]

Good to hear! Thanks for posting back.