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

[aequitas1903]

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.

 

[Andrew Poulsom]

In C2 copied down:

=IF(COUNTIF(A$2:A$4,"*"&B2&"*"),"A"&MAX(INDEX(ISNUMBER(SEARCH(B2,A$2:A$4))*(ROW(A$2:A$4)-ROW(A$2)+1),)),"Not Found")

 

[ttdk1]

in C1:

=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

 

[XOR LX]

In C2 copied down:

=IF(COUNTIF(A$2:A$4,"*"&B2&"*"),"A"&MAX(INDEX(ISNUMBER(SEARCH(B2,A$2:A$4))*(ROW(A$2:A$4)-ROW(A$2)+1),)),"Not Found")

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

 

[Aladin Akyurek]

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

C1, just enter and copy down:

=IF($A1="","",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

D1, just enter and copy down:

=IF($A1="","",
  IFERROR(ADDRESS(LOOKUP(9.99999999999999E+307,
   FIND("."&$B1&".","."&REPLACE($A$1:$A$3,1,$C$1:$C$3,"")&"."),ROW($A$1:$A$3)),
   COLUMN($A$1:$A$3)),"Not Found"))

Othwerwise, see Andrew's suggestion.

 

[aequitas1903]

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

 

[Aladin Akyurek]

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?