Search String in Multiple Cells

[hemi_0539]

I have a spreadsheet with 2 tabs. the first one has 2 columns, the first one a unique ticket # and the 2nd column has a comment box; free text field. the 2nd tab shows another unique ticket #.

what i want to do is use a formula next to the unique ticket # on the 2nd tab (B2) and search any instance of the ticket # (B1) on the tab Sheet1, second column with the free text field, range, B2:B2294.

basically for now i just want it to return a yes/ no if the data from the 2nd tab (B2) appears anywhere in that range of cells on Sheet1.

if i can get that to work, then i would like to take it up a notch and have it return which cell that data resides on the Sheet1 tab.

I have basic excel formula skills, but get frustrated easily when it doesnt work. I tried this in access, but i'll need to use sql and i really dont know sql. i was hoping a "more simplier" excel formula would accomplish this task.

any help would be greatly appreciated on what formula to use and how to use it!!!

Thanks

 

[Domenic]

Try...

Sheet2!B2:


=IF(ISNUMBER(MATCH(Sheet2!B1,Sheet1!$B$2:$B$2294,0)),"Yes","No")

Sheet2!B3:

=IF(B2="Yes",SUBSTITUTE(CELL("address",INDEX(Sheet1!$B$2:$B$2294,MATCH(Sheet2!B1,Sheet1!$B$2:$B$2294,0))),"$",""),"")

 

[hemi_0539]

I made some headway with those formulas, thats what i need, the only thing though that i may have not mentioned, but on Sheet1 B2:B2294, that cell reference from Sheet2 A2 that i am looking up works only if that exact # is in that cell on the Sheet 1 B2:B2294. The problem is that on the Sheet 1 B2:B2294 that tkt # from Sheet2 A2 can be in any of those cells on Sheet1 (column b) and not in any specific order. Sheet1 B2:2294 is a comments field when a ticket is closed out and there are lengthy notes on some of them but i need to know when that ticket # from Sheet2 A2...appears. I did like it showing Yes or No and on C3 showing the reference on where it was found at.

Basically i was wondering if there can be some sort of wild card search within that command, because it worked when i had the exact # in Sheet 1.

Thanks again for your help,

 

[Domenic]

Try replacing each instance of...

Sheet2!B1

with

"*"&Sheet2!B1&"*"

 

[hemi_0539]

That worked awesome, it found what i was looking for.

Here's what i used in Sheet2 B2:
=IF(ISNUMBER(MATCH("*"&Sheet2!A2&"*",Sheet1!$B$2:$B$2294,0)),"Yes","No")

and in Sheet2 B3:
=IF(B2="Yes",SUBSTITUTE(CELL("address",INDEX(Sheet1!$B$2:$B$2294,MATCH("*"&Sheet2!A2&"*",Sheet1!$B$2:$B$2294,0))),"$",""),"")

Now i have one more question, what i did to make things simple for my self, i had a spreadsheet with 8 different columns and i seperated them into 8 different spreadsheets to make my caluclations (what i had on Sheet1 column b).

I want to use the one with 8 columns, now i want to select B2:B2294 through I2:I2294.

Currently this part of it, Sheet1!$B$2:$B$2294, selects all of column b. how would i phrase it to change to select B through I?

thanks again for your help and quick response!!!

 

[Domenic]

Assuming that Sheet1!$B$2:$I$2294 contains unique ticket numbers (no duplicates), try...


=IF(Sheet2!A2<>"",IF(COUNTIF(Sheet1!$B$2:$I$2294,"*"&Sheet2!A2&"*")>0,"Yes","No"),"")

and

=IF(B2="Yes",SUBSTITUTE(CELL("address",INDEX(Sheet1!$B$2:$I$2294,SMALL(IF(ISNUMBER(SEARCH(Sheet2!A2,Sheet1!$B$2:$I$2294)),ROW(Sheet1!$B$2:$I$2294)-ROW(Sheet1!$B$2)+1),1),SMALL(IF(ISNUMBER(SEARCH(Sheet2!A2,Sheet1!$B$2:$I$2294)),COLUMN(Sheet1!$B$2:$I$2294)-COLUMN(Sheet1!$B$2)+1),1))),"$",""),"")

...confirmed with CONTROL+SHIFT+ENTER.

 

[hemi_0539]

Ill try that when I get back to the office Monday. It should work that first column on Sheet1 are unique. Thanks again for your help.