# Formula to reproduce cell location?

#### msimone

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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

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

Hope this helps!

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

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.

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!

Replies
4
Views
168
Replies
3
Views
139
Replies
5
Views
221
Replies
6
Views
791
Replies
1
Views
220

1,219,914
Messages
6,150,936
Members
450,994
Latest member
MacOrch

### 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?

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