Excel find next value in range ending with "xxx" and not already added

Question

I have a list (F:F) of data and would like to pull the next cell into column D with the following requirements:

Must end in "xxx" or "yyy" {OR(RIGHT(xx,10)="xxx", RIGHT(xx,10) = "yyy")}

Must not match anything already in D:D matching LEFT(xx,10)

I can use INDEX/MATCH for finding a valid match, however I cannot seem to find a formula to cross-check already inserted data. Ideally it would poll the list either of two ways:

Starting at the last inserted item's row and check for the next matching RIGHT test; ignore if already added and then find the 2nd matching RIGHT test; ignore if already added and then find the 3rd matching RIGHT test...etc

Starting at the last inserted item's row and check for the next unused item failing the LEFT test, then check if matches RIGHT test; repeating as necessary.

Answer 1

Instructions are not clear. Anyway, try this:

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
			=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$2:$F$7)/((RIGHT($F$2:$F$7,3)="xxx")+(RIGHT($F$2:$F$7,3)="yyy")),ROWS($D$2:D2))),"")		axxx
			=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$2:$F$7)/((RIGHT($F$2:$F$7,3)="xxx")+(RIGHT($F$2:$F$7,3)="yyy")),ROWS($D$2:D3))),"")		b
			=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$2:$F$7)/((RIGHT($F$2:$F$7,3)="xxx")+(RIGHT($F$2:$F$7,3)="yyy")),ROWS($D$2:D4))),"")		cxxx
			=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$2:$F$7)/((RIGHT($F$2:$F$7,3)="xxx")+(RIGHT($F$2:$F$7,3)="yyy")),ROWS($D$2:D5))),"")		d
			=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$2:$F$7)/((RIGHT($F$2:$F$7,3)="xxx")+(RIGHT($F$2:$F$7,3)="yyy")),ROWS($D$2:D6))),"")		eyyy
			=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$2:$F$7)/((RIGHT($F$2:$F$7,3)="xxx")+(RIGHT($F$2:$F$7,3)="yyy")),ROWS($D$2:D7))),"")		f