Need a formula to find a value in a range of both rows and columns

[PFCSC]

Hi there,

I am hoping someone could help me come up a formula that would allow me to find a cell in a range of both columns and rows.

I am trying to find an address say "123 New York, NY" and then return who lives at that address. The trouble is "123 New York, NY" could be anywhere in the range B2:CC8 and once the formula finds it, I want to return who lives at that address which is available in column A.

I'm not sure how this will look, but here's an image of what I'm trying to do:

	A	B	C	D	E	F	G	...	CC
1	Name	Address1	Address2	Address3	Address4	Address5	Address6	...	Address77
2	Peter	111 Dallas, TX	321 Los Angeles, CA	123 Chicago, IL	555 Houston, TX	111 San Francisco, CA	22 San Diego, CA
3	Sam	99 Omaha, NE	123 New York, NY
4	Greg	123 Miami, FL
5	Sarah	345 Anchorage, AK	555 Boston, MA	999 Baltimore, MD
6	George	666 Washington, D.C.
7	Michael
8	Anna

<tbody>
</tbody>

I would like a formula that could search B2:CC8 and find "123 New York, NY" and then return Sam.

Any help?

Thanks!

 

[L. Howard]

Maybe start with something like this.

Howard

Option Explicit
Sub Test_Address()
     
    Dim rFind As Range
    Dim myAddress as String, aCol As String
    
   myAddress = InputBox("Enter ADDRESS to search for")
     
    With Cells
        Set rFind = .Find(What:=myAddress, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            
            aCol = rFind.Offset(, -(rFind.Column - 1))
           
            MsgBox aCol

        End If
    End With
     
End Sub

Hmm, I overlooked the request for formula. Sorry.

 

[PFCSC]

Thank you for the reply!

Since I am going to be using this for hundreds of addresses (not just "123 New York, NY"), it would be ideal to not have to manually enter the store address into an InputBox each time, I'll need something that can work in bulk.

I have another tab with every address in column A and in Column B I want to know who is at that address by looking it up in the table previosuly described.

Thanks!

 

[L. Howard]

Still not a formula, but this does about 5000 rows in around seven seconds on my computer. There may be an Index/Match formula that can do this, but I am at a loss to develop one.

Names and address' on sheet 3 and the list of address' in column A on sheet 4, names are posted in column B of sheet 4..

Copy code to a standard module and run from sheet 4.


Howard

Option Explicit

Sub Test_Address_Code()
     
    Dim rFind As Range
    Dim Nme As String
    Dim OneRng As Range
    Dim c As Range

' List of names in sheet4 change sheet name to match
Set OneRng = Sheets("Sheet4").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each c In OneRng

     'all the address' & names are in sheet3 change sheet name to match
    With Sheets("Sheet3").Cells

        Set rFind = .Find(What:=c, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

        If Not rFind Is Nothing Then
            
           c.Offset(, 1) = rFind.Offset(, -(rFind.Column - 1))
          
        End If

    End With

Next

End Sub

 

[PFCSC]

It worked perfectly, you are a gentleman and a scholar, thank you!