Using data

Assuming you don't have more than 100000 lookup items on Sheet2, this represents your lookup range:

=Sheet2!A2:.B100000

Which is to say, from row 2 to the last non-empty row in columns A and B when considered together. 

So, using LET and XLOOKUP, and assuming you don't want to use a Table, place this formula in cell D2 of Sheet1:

=LET(
  lookupData, Sheet2!A2:.B100000,
  lookupArray, TAKE(lookupData,,1),
  returnArray, TAKE(lookupData,, 2),
  XLOOKUP(Sheet1!C2:.C100000, lookupArray, returnArray, "Comment ID not found")
)