Hi
You would need VBA for this. See code below.
Sub DualFind()
Dim vFind1 As String, vFind2 As String
Dim rFound As Range, lLoop As Long
Dim bFound As Boolean
Dim rLookIn1 As Range, rLookIn2 As Range
If Selection.Columns.Count <> 2 And Selection.Areas.Count = 1 Then
MsgBox "Must select 2 columns to search", vbOKOnly, "ozgrid.com"
Exit Sub
ElseIf Selection.Areas.Count = 1 Then
MsgBox "Must select 2 columns to search", vbOKOnly, "ozgrid.com"
Exit Sub
End If
vFind1 = InputBox("Find What: First value?", "FIND FIRST VALUE")
If vFind1 = vbNullString Then Exit Sub
vFind2 = InputBox("Find What: Second value?", "FIND SECOND VALUE")
If vFind2 = vbNullString Then Exit Sub
If Selection.Areas.Count > 1 Then
Set rLookIn1 = Selection.Areas(1).Columns(1)
Set rLookIn2 = Selection.Areas(2).Columns(1)
Else
Set rLookIn1 = Selection.Columns(1)
Set rLookIn2 = Selection.Columns(2)
End If
Set rFound = rLookIn1.Cells(1, 1)
For lLoop = 1 To WorksheetFunction.CountIf(rLookIn1, vFind1)
Set rFound = rLookIn1.Find(What:=vFind1, _
After:=rFound, LookAt:=xlWhole)
If UCase(rLookIn2.Cells(rFound.Row, 1)) = UCase(vFind2) Then
bFound = True
Exit For
End If
Next lLoop
If bFound = True Then
MsgBox "Match found", vbInformation, "ozgrid.com"
Range(rFound, rLookIn2.Cells(rFound.Row, 1)).Select
Else
MsgBox "Sorry, no match found", vbInformation, "ozgrid.com"
End If
End Sub
To use the code, push Alt+F11 and go to Insert>Module. Now paste in the
code below. Click the top right X to get back to Excel and Save.
Now assign the macro to a control button, textbox etc or push Alt+F8 and
click the macro "DualFind" the "Run"
** Posted via:
http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum
http://www.ozgrid.com/forum ***
