How can I define area for MATCH or VLOOKUP function?

In Calc, I am attempting to make a function that will search within an area that is defined by another cell. More precisely, I want to be be able to enter the name of the sheet.
As an example, suppose I want to find the row in area Sheet2.A1:A50 where the cell has the value “apple”. I could do this with the function
=MATCH(Sheet2.A1:A50;“apple”;0)
which would return the row number for “apple” if it exists, and return an area if it does not.
However, I would like to apply this to a series of sheets with different names, so I would like to get the sheet name (“Sheet2” in the example) from a different cell. I have tried things like
=MATCH(B1&".A1:A50";“apple”;0)
to get the sheet name from the contents of cell B1. But I only get an error message.

Is a thing like this possible, and how?

0: The row can only be found if “apple” does not occur a second time in the search range.
__Otherwise you get the first occurrence top down (or left to right if applicable).
1: The value to search for must go to the first parameter position of MATCH.
2: To convert an address string into a reference use INDIRECT.
3: Often the OFFSET function offers more flexible ways to get range references.

=MATCH("apple" ; INDIRECT(B1 & ".A1:A50") ; 0)