Google Appscript partial vlookup-CodePudding

I am very new to appscript and any help on the below query will really be helpful.

I am having data in Sheet1 in column A and Column B, now I want to design an appscript which get the below job done.

(1) Sheet1 Column A has Roll number along with student name i.e. Roll No.*Name, column B has actual value (these both columns are user input).

(2) Sheet2 has two columns Roll No. and Dummy value.

(3) In Sheet1 column C, I want the dummy value from Sheet2 column B by matching the Roll number of both sheets, however, I want value only in those rows in which actual value (column B) in Sheet1 is blank.

For reference I am sharing the link of the sheets.

Put this code into Column C2 in sheet1.
This code lookup for matches of 'Roll No.' from sheet2 column A whenever there Column A is not empty and Column B is empty in sheet1.

=ArrayFormula(
 IFS(
  $A$2:$A="","",
  $B$2:$B<>"","",
  TRUE,XLOOKUP(INDEX(SPLIT($A$2:$A,"*"),,1),sheet2!$A:$A,sheet2!$B:$B)
 )
)

Solution - 2. write a costume function in apps-script:

in-app call:

apps-script js code:

this solution create a costume function called 'MYLOOKUP()' with apps-script, and call the function form the spreadsheet.

// apps-script:

function MYLOOKUP(data1,data2) {
  return data1
    .map(([rollNo_Name,value]) => {
      return (rollNo_Name !== '' && value === '') ?
        data2.find(([rollNo,]) => rollNo_Name.split('*')[0] == rollNo)[1] :
        ''
    });
}

in-app call:

=MYLOOKUP(A2:B,sheet2!A:B)

There are a lot other solution to your question if you metion apps-script, such as...

instead of calling the costume function in-app, you can instead create a costume menu to run the function when you click on it,

or add simple trigger so that evertime the spreadsheet is edited, the function will be run one time.

you can even create a web ui for this thing to run, etc.