# Formula to find cell value in a Range and show the adress

#### aequitas1903

##### Board Regular
Hello Everyone,

I do have a data in which column A has lots of information in one cell. and column B has numeric information. I do want to search values in column B in column A. I need a formula in column C to show me the adress of the found data. There is an example below.

 ColumnA ColumnB ColumnC 1 Ben2123.52.921.30.09.2013 5753 A3 2 John53234.12.534.01.08.2014 921 A1 3 Harry5753.54.524.23.04.2013 535 Not Found

<tbody>
</tbody>

Please let me know if it is possible to write a formula like this. Any help is appreciated.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
In C2 copied down:

in C1:

Code:
``=IF(SUMPRODUCT(NOT(ISERR(SEARCH(B1,\$A\$1:\$A\$3)))*ROW(\$A\$1:\$A\$3)),"A"&SUMPRODUCT(NOT(ISERR(SEARCH(B1,\$A\$1:\$A\$3)))*ROW(\$A\$1:\$A\$3)),"Not Found")``

copy down

In C2 copied down:

I'm not sure quite what the OP is after, but if cell A3 contained, e.g.:

John8892188.12.534.01.08.2014

then the above formula would favour this value when searching for 921, which may not be the desired result.

I get the impression that the values being searched are intended to be those delimited by dots, and not subsets of those values, though I may be wrong.

If that is the case, then the fact that the first value in the string does not have a dot immediately preceding it makes the solution a touch problematic.

@aequitas1903 Can you confirm?

Regards

Last edited:
In case the number to look for must be delimited by dots, try the following:

C1, just enter and copy down:
Rich (BB code):
``````=IF(\$A1="","",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
``````

D1, just enter and copy down:
Rich (BB code):
``````=IF(\$A1="","",
FIND("."&\$B1&".","."&REPLACE(\$A\$1:\$A\$3,1,\$C\$1:\$C\$3,"")&"."),ROW(\$A\$1:\$A\$3)),
``````

Othwerwise, see Andrew's suggestion.

Thank you everyone.

I tried all of your suggestions and @ttdk1 's idea gave me what I look for.

@XOR LX The struggle is that data is mixed and contains different values in one cell only and it is not in an order. I got a list which has unique values and was trying to find the exact location.

Thanks to everyone who ever tried to help me. I admire you guys

Thank you everyone.

I tried all of your suggestions and @ttdk1 's idea gave me what I look for.

@XOR LX The struggle is that data is mixed and contains different values in one cell only and it is not in an order. I got a list which has unique values and was trying to find the exact location.

Thanks to everyone who ever tried to help me. I admire you guys

Why don't you test the suggestions with something like 21 in B?

Replies
5
Views
1K
Replies
7
Views
2K
Replies
11
Views
1K
Replies
11
Views
1K
Replies
15
Views
348

1,203,097
Messages
6,053,519
Members
444,669
Latest member
Renarian

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

### Which adblocker are you using?

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

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