Iterate over column to find if a value is in there? (Google Sheets)

Question

In a column I want to iterate over say row 1-7 to see if it contains value A. If it contains value A I want to print value C. If FALSE I want to check say row 8 - 10,and if TRUE i want to print value D. If FALSE I want to continue to row 11 - 20, and if TRUE print value E and so on... See this link for clarification: https://i.stack.imgur.com/8XHe1.jpg

I've tried using an IFS but when I do I get an error basically saying that the formula expected 21 rows but only got 11, the reason being the first time I iterate over the list it's checking 21 rows, and the next time it's 11, the time after that it might be 6 rows and so on. Any idea how to solve this issue?

Right now I've had do do each and every row manually which is insanely time consuming. Thanks.

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(E2; IF(A2:A="";; {A2:A\ 
 VLOOKUP(SORT(ROW(A2:A); 1; 0); IF(SORT(B2:B; ROW(A2:A); 0)<>""; 
 {ROW(A2:A)\ SORT(B2:B; ROW(A2:A); 0)}); 2; 1)}); 2; 0)))