This formula should look up in the A column of multiple sheets and when the match is found (in this case there are not duplicates in any A column of the different sheets), it gives back the value found in the cell next on the right to the match.
=ArrayFormula(IF(LEN(B5),VLOOKUP(B5,{SHEET1!$A$3:$B,SHEET2!$A$15:$B},2,FALSE),""))
But it gives a mismatch error and even if I make the ranges the same length the value in B5 is searched only into the first sheet of the range, in this case SHEET1!$A$3:$B.
Is is possible to make the formula work with ranges of different length from multiple sheets?
always when you construct the virtual array with array brackets {} both sides needs to be of the same size.
={A1:A10, B1,B10}
or:
={A1:C10; D1:F10}
in your case, the array literal error comes from mismatched rows when you use "infinite" rows by not specifying the end row. eg your sheet1 has more or fewer rows then your sheet2
=INDEX(IF(LEN(B5), VLOOKUP(B5, {SHEET1!A3:B; SHEET2!A15:B}, 2, 0), ))