excel search for characters not listed in rows above

[intliteracylinguist]

In this worksheet, the stories (column D) are only allowed to use letters that have already been learned (column B). The story does not need to include all of the previous letters. The story may not use letters that have not yet been taught. When I type a story into column D, I would like column E to tell me if the story contains letters that are not listed in rows above. Can you help me? Thanks!

  

[#2]

@intliteracylinguist 

Unmerge the cells in column E.

Enter the following formula in E2:

=IF(A2="", "", LET(sentence, SUBSTITUTE(D2, " ", ""), length, LEN(sentence), used, MID(sentence, SEQUENCE(length), 1), last, IFERROR(MATCH(A2+1, A:A, 0)-1, 10000), colB, INDIRECT("B2:B"&last), letters, CONCAT(FILTER(colB, colB <>0)), valid, AND(ISNUMBER(SEARCH(used, letters))), IF(valid, "", "Invalid")))

Fill down.

[#3]

@intliteracylinguist 

=IF(SUM(N((ISNUMBER(SEARCH(B5:$B$12,D4))))),"invalid","")

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. In cell B12 is a search value which isn't a letter and therefore doesn't appear in a story in column D.

[#4]

@Hans Vogelaar 

I'm getting 'Invalid' for all rows with story text. In this example, only D14 is invalid.

[#5 · Accepted solution]

@intliteracylinguist  so that original example/sheet image was showing merged cells which cause lots of problems.  Also having varying number of rows per group causes a bit of complexity.  The following formula works but is extra complicated because of that and assumes a max of 10 rows for the last added letter group:

=LET(letters,$B$2:INDEX(B:B,IFERROR(XMATCH(A2+1,A:A),ROW(A2)+10)-1),
  IF(TRIM(CONCAT(TEXTSPLIT(D2,FILTER(letters,LEN(letters)>0,""),,FALSE,1)))="",
     "Valid",
     "InValid"))

 in the attached is both merged table and unmerged.  In the unmerged you can more easily copy and paste the formula down but you can't on the merged

[#6]

Perfect! Thank you so much! This will save me a lot of time manually checking.