Excel Query - Matching more than variable

[Closed]
Report
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Hello,


Hope you can help!

I am trying to match two values in two seperate columns and then return the value in the third column? For instance if column A is forenames, Column B is surnames and Column C is an address, although there are duplications in column A & B individually there is only one John Smith.
I'd like to avoid using &" "& to join Column A & B together to create one data column to search through but can't think of any other way. Thanks in advance

Cheers
Kit

1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Kit,

See if this is to your liking:

Sub SearchAddress()
Dim fName, lName As String
Dim lRow As Integer
fName = InputBox("Please input first name.")
lName = InputBox("Please input last name.")
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A1:A" & lRow)
If cell.Value = fName And cell.Offset(0, 1).Value = lName Then
MsgBox cell.Offset(0, 2).Value, vbOKOnly, "The address for " & fName & lName & " is:"
End If
Next cell
End Sub

Best regards,
Trowa