Google Sheets: lookup a value in a table then find right most non-blank value in a range

Question

I would like to create a google sheet formula that will lookup the ID and return the right-most non-blank score from columns 'Score A', 'Score B' and Score 'D'. Any help would be much appreciated!

ID	Score A	Score B	Score C	Type
2342	65	43		A
8797				B
2343	23	45	98	F
6666	23			B
2333	67	43		B

Answer 1

Assuming E1 is your search_key here's what you can try:

=LET(ζ,TOROW(XLOOKUP(E1,A2:A,B2:D),1),INDEX(ζ,,COUNTA(ζ)))