How to do a Vlookup for each row in Google Sheets?

Question

I'm creating a Google Sheets to track scores in an event. I have a sheet for each day where we write the name of each player that played that day and the result of each game in which he played. There's a last column with that total. I'm trying to create a sheet that would centralize all the important results. For now, I can get all the players name but my issue is to get their totals. I managed to get more or less the result that I want with:

=IF(COUNTIF('05-11'!$A$2:$A$35;$A2)>0;VLOOKUP($A2;'05-11'!$A$2:$R$35;17;false);)

The issue is that I need to drag the formula on each row. I would like to write a formula that could insert in the first cell an that would query all totals in the cells below. I think I need something like this but it returns a Value Error:

=QUERY('05-11'!A2:R;"SELECT R WHERE A MATCHES "&A2:A;)

Keeping in mind that all the players that appears in that centralizing sheet might not have participate that day.

Here's an example of the document with some data: https://docs.google.com/spreadsheets/d/1twyIsHxc1YcTxFjWq8JzOAC1lUKO04j_qNhX2a9y8Vg/edit?usp=sharing

Answer 1

in your test sheet:

=QUERY(Tableau!A1:F; "SELECT A,B WHERE NOT B MATCHES "&TEXTJOIN("|"; 1; C2:C); 1)

should be:

=QUERY(Tableau!A1:F; "SELECT A,B WHERE NOT B MATCHES '"&TEXTJOIN("|"; 1; C2:C)&"'"; 1)

in your Calc Points sheet use this in C2 and drag to the right (ofc after you delete all formulae in C2:Z range:

=ARRAYFORMULA(IFNA(VLOOKUP($A2:$A; INDIRECT(REGEXEXTRACT(TO_TEXT(C1); "\d+-\d+")&"!A2:R41"); 18; 0)))