search text

[Sophearith]

Dear Exceller,
I have data in multiple columns say from A-E
I have name in such columns
A B C D E
1 Kane Jack Mona David Roza
2 John Kate Rosa Jinny Mina
3 Saha Sine Hovid Kani Hana
4 Rose Hidy Howdy Hatha Sizy
I want to search word "Kani" in cell A10 and return cell address in B10.in this table the result cell address:D3
hope it helps,need both formula and vba
regards

 

[Nishant94]

Book1
	A	B	C	D	E
1	Kane	Jack	Mona	David	Roza
2	John	Kate	Rosa	Jinny	Mina
3	Saha	Sine	Hovid	Kani	Hana
4	Rose	Hidy	Howdy	Hatha	Sizy
5
6
7
8
9
10	kani	D3
11
12
Sheet2

Cell Formulas
Range	Formula
B10	{=ADDRESS(MIN(IF(ISNUMBER(SEARCH(A10,$A$1:$E$4)),ROW($A$1:$E$4))),MIN(IF(ISNUMBER(SEARCH(A10,$A$1:$E$4)),COLUMN($A$1:$E$4))),4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Logit]

.

Download Workbook : https://www.amazon.com/clouddrive/share/Lemuv6RcLdA4naZpoCyYzPgyEp7iSjmjOYy0jwYcy4f

Option Explicit


Sub FindInLists()
     '
     '
     '
     
    Dim SheetsToSearch, SrchStrg As String, ws As Excel.Worksheet, r As Range
     Set ws = Sheets("Sheet1")
    SrchStrg = Application.InputBox("Enter Term to search ", "Search Term", Type:=2)
    
    With ws
        
            With ws.Range("A2:AB5000")  'EDIT RANGE AS REQUIRED
                Set r = .Find(what:=SrchStrg, After:=.Range("A1"))   'find the cell whose value is equal to SrchStrg and activate it
                If Not r Is Nothing Then
                    ws.Activate: r.Activate
                    ws.Range("B1").Value = SrchStrg
                    ws.Range("D1").Value = ActiveCell.Address
                                       
                ElseIf r Is Nothing Then
                    MsgBox "Search term does not exist. ", vbInformation, "Item Not Found"
                End If
            End With
        
    End With
     
End Sub

	A​	B​	C​	D​	E​	F​
1​	Search Term :	Sally​	Location :	$A$18​
2​
3​
4​
5​
6​
7​		1​
8​
9​
10​
11​
12​			Timothy
13​
14​
15​
16​
17​
18​	Sally
19​