Joining tables in R based on multiple but incomplete ID columns by using ifelse statements

I have the following problem. For a project I have (pseudonymized) student data from a number of different schools. I combined the different files into one big data frame. Something like the following table, which contains a few (unique) identifiers (e.g. student number, name_code):

table1<-tribble(~Name_code, ~Student_number,    ~School,    ~Other_variables_I_measured,
    "Name_1", 123456, "A",  0.360,
    "Name_2", 234567, "A",  0.813,
    "Name_3", 345678, "A",  0.518,
    "Name_4", 456789, "A",  0.048,
    "Name_5", 567900, "A",  0.096,
    "Name_6", 679011, "B",  0.319,
    "Name_7", 790122, "B",  0.704,
    "Name_8", 901233, "B",  0.574,
    "Name_9", 112344, "B",  0.662,
    "Name_10", 123455, "B", 0.178)

(table1)

Name_code   Student_number  School  Other_variables_I_measured
Name_1      123456             A    0.360
Name_2      234567             A    0.813
Name_3      345678             A    0.518
Name_4      456789             A    0.048
Name_5      567900             A    0.096
Name_6      679011             B    0.319
Name_7      790122             B    0.704
Name_8      901233             B    0.574
Name_9      112344             B    0.662
Name_10     123455             B    0.178

At the end of the academic year the schools provided additional data (GPA, retention, etc.), however, depending on the school, the individual student reports might only include one of the identifiers, which makes it hard to link the information to table 1 as each of the identifiers includes missings when I combine the grade data. E.g. reports of school A might only include the student number, reports of school B only the (recoded) name.

table2<-tribble(~Student_number,    ~Name_code, ~GPA,   ~School,
    123456,     NA,     8,  "A",
    234567,     NA,     9,  "A",
    345678,     NA,     7,  "A",
    456789,     NA,     8,  "A",
    567900,     NA,     7,  "A",
    NA,     "Name_6",   4,  "B",
    NA,     "Name_7",   5,  "B",
    NA,     "Name_8",   4,  "B",
    NA,     "Name_9",   5,  "B",
    NA,     "Name_10",  7,  "B")

(table2)

Student_number  Name_code   GPA School
123456             NA         8    A
234567             NA         9    A
345678             NA         7    A
456789             NA         8    A
567900             NA         7    A
  NA             Name_6       4    B
  NA             Name_7       5    B
  NA             Name_8       4    B
  NA             Name_9       5    B
  NA             Name_10      7    B

Is there a way to join table 1 and 2 based the different, complete ID values? (= joining by ID variable 1, and if missing, use ID variable 2 instead)

Some pseudo code like:
    dplyr::left_join(table1, table2, by= (ifelse(!is.na(Student_number), "Student_number", "Name_code"))))

which should produce table3

Name_code   Student_number  School  Other_variables_I_measured  GPA
Name 1      123456            A      0.360                  8
Name 2      234567            A      0.813                  9
Name 3      345678            A      0.518                  7
Name 4      456789            A      0.048                  8
Name 5      567900            A      0.096                  7
Name 6      679011            B      0.319                  4
Name 7      790122            B      0.704                  5
Name 8      901233            B      0.574                  4
Name 9      112344            B      0.662                  5
Name 10     123455            B      0.178                  7

What would be the easiest way to do this? Or are there even functions that can circumvent the ifelse part completely?