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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Domenic

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

Forum statistics

Threads
1,141,934
Messages
5,709,413
Members
421,635
Latest member
mehdi hannechi

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top