Drag and copy with auto-Cell reference adjustment?

[RHH1095]

Is there a way to drag and copy and automatically adjust cell references for the following expression as I describe below.

This formula is in Cell A7...
=ADDRESS(MIN(IF(A1:F6=A13,ROW(A1:F6))),MIN(IF(A1:F6=A13,COLUMN(A1:F6))),4)

When I drag it to Cell B7 I would like it to become....
=ADDRESS(MIN(IF(G1:L6=B13,ROW(G1:L6))),MIN(IF(G1:L6=B13,COLUMN(G1:L6))),4)

As you can see I would like A1:F6 to adjust to G1:L6 (By 6 cells) and A13 adjust to B13 (By 1 cell).

 

[DonkeyOte]

If you wish to retrieve the first cell containing a given value then the original is slightly flawed if one allows for duplicates

Assume A2 and B1 contain A13 - the original would return A1 as first match (min row being 1, min column being 1)

In reality you first need to decide whether or not row or column should take precedent - ie in above scenario which prevails A2 or B1 ? (column vs row)

Assuming row should prevail one possibility would be:

=CELL("address",INDIRECT("R"&SUBSTITUTE(MIN(IF(OFFSET($A$1:$F$6,0,6*(COLUMNS($A7:A7)-1))=A$13,ROW($A$1:$A$6)+COLUMN(OFFSET($A$1:$F$6,0,6*(COLUMNS($A7:A7)-1)))/1000000)),".","C"),FALSE))
confirmed with CTRL + SHIFT + ENTER
(enter alone will not suffice)

It should be noted however that the above is both an Array and Volatile and thus used en masse would perform poorly.


EDIT:

question also asked on OPs original thread: http://www.mrexcel.com/forum/showthread.php?p=2952371

 

[RHH1095]

First thank you for your answer. It does work! How do I edit this solution to remove the absolute cell reference. i.e. $ signs on what is returned.

My orginal formula had a switch of 4 to remove. Not sure what to do with Cell function.

=ADDRESS(MIN(IF(A1:F6=A13,ROW(A1:F6))),MIN(IF(A1:F6=A13,COLUMN(A1:F6))),4)

 

[DonkeyOte]

You might wrap within a SUBSTITUTE

=SUBSTITUTE(CELL(....),"$","")
confirmed with CTRL + SHIFT + ENTER

(per earlier formula XL2007 or above is assumed)

 

[RHH1095]

Why does the solution you suggested only work the next 8 cells to the right of the initial cell?.

I forgot to say earlier that there were no dupicates.

=SUBSTITUTE(CELL("address",INDIRECT("R"&SUBSTITUTE(MIN(IF(OFFSET($B$2:$G$7,0,6*(COLUMNS($B7:K7)-1))=K22,ROW($B$2:$B$7)+COLUMN(OFFSET($B$2:$G$7,0,6*(COLUMNS($B7:K7)-1)))/10000000000)),".","C"),FALSE)),"$","")

 

[DonkeyOte]

Perhaps:

=SUBSTITUTE(CELL("address",INDIRECT("R"&SUBSTITUTE(TEXT(MIN(IF(OFFSET($B$2:$G$7,0,6*(COLUMNS($B7:K7)-1))=K22,ROW($B$2:$B$7)+COLUMN(OFFSET($B$2:$G$7,0,6*(COLUMNS($B7:K7)-1)))/1000000)),"0.000000"),".","C"),FALSE)),"$","")
confirmed with CTRL + SHIFT + ENTER

If as you say you have no dupes you could "simplify" to:

=ADDRESS(MIN(IF(OFFSET($B$2:$G$7,0,6*(COLUMNS($B7:K7)-1))=K22,ROW($B$2:$B$7))),MIN(IF(OFFSET($B$2:$G$7,0,6*(COLUMNS($B7:K7)-1))=K22,COLUMN(OFFSET($B$2:$G$7,0,6*(COLUMNS($B7:K7)-1))))),4)
confirmed with CTRL + SHIFT + ENTER

however, with more thought I'm sure you could streamline - I'm rushing off I'm afraid.

 

[RHH1095]

Thank you Sir! That worked perfectly and solved some other issues.