Paul Naylor
Board Regular
- Joined
- Sep 2, 2016
- Messages
- 98
- Office Version
- 365
- 2003 or older
- Platform
- Windows
- Mobile
- Web
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 ?
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 ?