I'm looking to create a Foosball sheet for my office. We enter scores via Typeform. When a match is entered in Typeform, a new row is created at the bottom of the sheet with the appropriate scores. I'm looking to use =ARRAYFORMULA so that functions are run on any newly-created rows.

Since there are only 2 players in foosball, I need to calculate the winner and loser. The winner is easy: person with 10 points. The loser is not as simple. In theory, the Loser is the person with 0-9 points. I determined a way to calcucate this for our 3-person office (in the sheet:

=arrayformula(if(isblank(A2:A),, if(isblank(B2:B),if(C2:C<D2:D,"David","Twok"),if(isblank(C2:C),if(B2:B<D2:D,"Bim","Twok"),if(isblank(D2:D),if(B2:B<C2:C,"Bim","David"))))))

However, we are adding 4 new players. As we scale players up, IF/ELSE logic becomes exponentially unwieldy. I am not certain how to figure out the most efficient (see: not a billion =ISBLANK() and =IF() statements) way to calculate the Loser.

I was thinking about something like:

=ARRAYFORMULA(IF(ISBLANK(A2:A),,LARGE([somehow get an array of numbers in a specific row],2)))

Then somehow getting that column number, and running an HLOOKUP to get the name of the person who earned the Loser score.

I can easily work these formulas outside of an arrayformula, though my constraint of adding new rows automatically and needing formulas to run on them makes things difficult.

The sheet is in view-access

here.