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

aequitas1903

Board Regular
Joined
Mar 8, 2012
Messages
127
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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ben2123.52.921.30.09.2013[/TD]
[TD]5753[/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John53234.12.534.01.08.2014[/TD]
[TD]921[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Harry5753.54.524.23.04.2013[/TD]
[TD]535[/TD]
[TD]Not Found[/TD]
[/TR]
</tbody>[/TABLE]



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

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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")
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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="","",
  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.
 
Upvote 0
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 :)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,226,498
Messages
6,191,377
Members
453,655
Latest member
lasvegasbuffet

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
Back
Top