# Create a reference to a row number that flexible

#### kytx88

##### New Member
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)

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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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>

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.

Replies
21
Views
437
Replies
4
Views
239
Replies
2
Views
171
Replies
4
Views
224
Replies
1
Views
444

### Forum statistics

1,221,053
Messages
6,157,638
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back