google sheets - How to return last ocurrence of a name in column A if the content in column B is not empty

Question

I'm trying so hard to develop a function that returns the last ocurrence of a name in column A if the content in column B is not empty. I illustrated the behavior of this in the image bellow:

The formula should:

• Ignore empty spaces in column A
• No need for changes if rows are added

I couldn't go further than that:

=INDEX(A3:B,SMALL(IF(A3:A=D2,ROW(A3:A)-ROW(A3)+1,ROW(INDIRECT("A"&COUNTA($A:$A)))+1),COUNTIF(A3:A,D2)),2)

Any help?

Thanks

Link to the sheet (GOOGLE SHEETS) in image: https://docs.google.com/spreadsheets/d/1tYmq4H4i9Y3LJnOOkT8XArNzEWnkhpEk1yDiBjvctRI/edit?usp=sharing

Answer 1

use:

=ARRAY_CONSTRAIN(SORTN(QUERY(SORT({A3:B ROW(A3:A)}; 3; 0); 
 "where Col2 is not null"; 0); 9^9; 2; 1; 1); 9^9; 2)