Formula to reproduce cell location?

msimone

New Member
Joined
Sep 30, 2006
Messages
17
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
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

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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
ABCDE
1535[Book4.xls]Sheet1!$A$7
210
315
420
525
630
735
840
945
10
Sheet1
 

msimone

New Member
Joined
Sep 30, 2006
Messages
17
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,992
Messages
5,545,373
Members
410,679
Latest member
rolandbianco
Top