Formula to reproduce cell location?

[msimone]

Is there a formula that can search for a value and then return the cell location of that find? For example, if I have the following:

A1: 5
B1: 10
C1: 15

A2: 20
B2: 25
C2: 30

...and in cell E1 I want to display the cell location for "20", is there a simple formula that can do that?

Thanks!

 

[Domenic]

Try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=CELL("address",INDEX(A1:C2,MIN(IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1)),MATCH(20,INDEX(A1:C2,MIN(IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1)),0),0)))

To return the cell location without the absolute reference, try the following formula instead...

=SUBSTITUTE(CELL("address",INDEX(A1:C2,MIN(IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1)),MATCH(20,INDEX(A1:C2,MIN(IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1)),0),0))),"$","")

Hope this helps!

 

[babycody]

Threw in a way to go to that cell by clicking on a hyperlink. As you can see 35 is the number I am looking for. Keep in mind that this will only work for the first match the formula finds. Any duplicates would not have their address returned. HTH!

Book4.xls
	A	B	C	D	E
1	5	35	[Book4.xls]Sheet1!$A$7
2	10
3	15
4	20
5	25
6	30
7	35
8	40
9	45
10
Sheet1

 

[msimone]

Thanks Dominic! If you've got time and it's not too annoying, could you breakdown how this works?

If not, no worries! Thanks so much.

 

[Domenic]

The first INDEX function is used to point to the cell housing the value of interest. The MIN(…) part of the formula provides us with the row number, and the MATCH(…) part of the formula gives us the column number. Here's how the MIN(…) part of the formula breaksdown…

ROW(A1:C2)-ROW(A1)+1 returns the following array of numbers:

1
2

IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1) returns the following array of values:

FALSE FALSE FALSE
2 FALSE FALSE

As you can see, for each cell that contains 20, the corresponding row number is returned. So here's how the MIN(…) part of the formula is evaluated…

MIN(IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1))

MIN(IF({5,10,15;20,25,30}=20,{1;2}-1+1))

MIN(IF({FALSE,FALSE,FALSE;TRUE,FALSE,FALSE},{1;2}))

MIN({FALSE,FALSE,FALSE;2,FALSE,FALSE})

…which returns 2, indicating we want Row 2. The MATCH(…) part of the formula is used to return the column number for the first INDEX function. The second INDEX is used to return a reference or array of values for the row indicated by MIN(…). Notice that while MIN(…) returns the row number, the column number is set to 0 which returns the reference for the entire row. MATCH then simply finds the position of 20 within this array of values. Here's how the formula breaks down...

MATCH(20,INDEX(A1:C2,MIN(IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1)),0),0)

MATCH(20,{20,25,30},0)

…which returns 1, indicating that we want Column 1. So now here's how the first INDEX is evaluated…

INDEX(A1:C2,MIN(IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1)),MATCH(20,INDEX(A1:C2,MIN(IF(A1:C2=20,ROW(A1:C2)-ROW(A1)+1)),0),0))

INDEX(A1:C2,2,1)

By itself, INDEX returns the value in A2. However, since it's wrapped within CELL("address"…) and it expects a reference, INDEX returns a reference. CELL then returns the cell address as $A$2. Lastly, SUBSTITUTE simply substitutes the $ signs with an null string or nothing and the formula returns A2.

Hope this helps!