Create a reference to a row number that flexible

[kytx88]

Hello forum,

I have a workbook with 2 worksheets. Sheet1 is a collection of tables brought together automatically. Some of the names of the headers are found in more than one table, some of the names of the headers are unique.
example:

Materials	Sault	Sand	Stone
	1	2	3
	4	5	6

<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

sales	Sand	Sault	Stone
	1	10	6

<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

every day the position of the tables changes (plus-minus few rows or columns)

In Sheet2 I am trying to compute the coordinates of "Sand" in the "Materials" table.
In my particular task "Materials" is a unique word so I can find the cow and column number using array functions:
{=MAX((Sheet1!$A$1:$Z$100=A3)*ROW(Sheet1!$A$1:$Z$100))} for the row and
{=MAX((Sheet1!$A$1:$Z$100=A3)*ROW(Sheet1!$A$1:$Z$100))} for the column
I need to know the coordinates of the "Sand" header I want
I also know that the "Sand" I want is on the same row as the unique "Materials" word, so for the row number I will use just the same number as for the "Materials"
I also know that in that row the word "Sand" is the only one, so for the column number I use:
=MATCH(A4,Sheet1!$8:$8,0) because today the word "Materials" is found in the row 8
if tomorrow it is found in row 10, I would have to manually change the formula to:
=MATCH(A4,Sheet1!$10:$10,0)
What I want to do is to calculate the row number (I am doing it anyway already) and then using INDIRECT put it into my MATCH, so that it looks something like:
=MATCH(A4,Sheet1!INDIRECT(Sheet2!$E$4):INDIRECT(Sheet2!$E$4),0)
I cannot work out the correct syntax to do that, please help.

If I could I would post my example file to show what my issue is.

 

[kytx88]

My Sheet2 looks like this:

looking for:			self-check	what row?	what column?
Materials			=INDEX(Sheet1!$A$1:$Z$100,E3,F3,1){	=MAX((Sheet1!$A$1:$Z$100=A3)*ROW(Sheet1!$A$1:$Z$100))}	{=MAX((Sheet1!$A$1:$Z$100=A3)*COLUMN(Sheet1!$A$1:$Z$100))}
Sand			=INDEX(Sheet1!$A$1:$Z$100,E4,F4,1)	=E3	=MATCH(A4,Sheet1!$8:$8,0)

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[kytx88]

I also cannot use OFFSET because in the real table the software that creates it can generously miss columns between "Materials" and "Sand", so Materials can be column X and "Sand" can be column AQ today BA tomorrow etc so I never know how many columns I need to skip. So have to use MATCH.