Perform a two column lookup to check to see if a name exists

[Mike in Phoenix]

I need to perform a two column lookup to check to see if a person's first and last name are already in the worksheet. I use forms to input student data and also to associate students with events and programs. Only the students first and last name are captured in the association process.I would like to prevent students from being associated with events or programs until the full student bio has been captured.
I have a sheet just for lookups and each time a student bio is completed the fist name goes in column J and the last name goes in column K on the same row, of course. Up to four students' names will need to be checked with one click event. The variables being used will be txtFirst1 and txtLast1 through 4.
So I need to check to see if the 4 pairs of txtFirst and txtLast match any row pair in columns J and K.
This will need to be done in VBA and not a spreadsheet formula.
Thanks in advance for your efforts!

 

[Ahnold]

Re: Perform a two column lookup to check to see if a name ex

Mike,

I'm still learning how to write code. I came up with this. Put this in your code after the variables are given values. Let me know if it works. If anyone want to critique it, I'm all ears.

First = Array(txtFirst1, txtFirst2, txtFirst3, txtFirst4)
    Last = Array(txtLast1, txtLast2, txtLast3, txtLast4)
    For i = 0 To 3
        On Error Resume Next
        a = WorksheetFunction.Match(First(i), Range("J:J"), 0)
        If Err.Number <> 0 Then MsgBox (First(i) & " " & Last(i) _
            & " is not on the list")
        If Err.Number <> 0 Then GoTo one:
        b = WorksheetFunction.Match(Last(i), Range("K:K"), 0)
        If Err.Number <> 0 Then MsgBox (First(i) & " " & Last(i) _
            & " is not on the list")
        If Err.Number <> 0 Then GoTo one:
        If a = b Then
            MsgBox (First(i) & " " & Last(i) & _
            " is already on the list")
        Else: MsgBox (First(i) & " " & Last(i) _
            & " is not on the list")
        End If
one:    Err.Clear
    Next i

 

[Yogi Anand]

Re: Perform a two column lookup to check to see if a name ex

hi Mike:

I have not tried Ahnold's fine contribution yet -- but I had a little play with the following code ...

Sub y_()
    For i = 1 To 4
    myRow = 0
    On Error Resume Next
        First = "txtFirst" & i
        Last = "txtLast" & i
        myRowF = WorksheetFunction.Match(First, [J:J], 0)
        myRowL = WorksheetFunction.Match(Last, [K:K], 0)
        If myRowF > 0 And myRowF = myRowL Then MsgBox ("Name " & i & " exists")
    Next i
End Sub

is it something like this what you are trying to do?

 

[Ahnold]

Re: Perform a two column lookup to check to see if a name ex

Mike,

I realized this morning that my code works functionally, but not logically. The problem is, it will only check the first match in column J. So if you are searching for Bob Smith and Bob Smith is in the list and Bob Jones comes first in the list it will return an incorrect message.

If you were to combine the first names with the last names into row L using the Concanetate Function, then this code would work.

   First = Array(txtFirst1, txtFirst2, txtFirst3, txtFirst4)
    Last = Array(txtLast1, txtLast2, txtLast3, txtLast4)
    For i = 0 To 3
        On Error Resume Next
        a = WorksheetFunction.Match(First(i) & Last(i), Range("L:L"), 0)
        If Err.Number <> 0 Then MsgBox (First(i) & " " & Last(i) _
            & " is not on the list")
        If Err.Number <> 0 Then GoTo one:
        If a > 0 Then MsgBox (First(i) & " " & Last(i) & _
            " is already on the list")
one:    Err.Clear
    Next i

The other thing I realized, what if you weren't searching for 4 names, but a different number of names each time. You could change the code to this.

   First = Array(txtFirst1, txtFirst2, txtFirst3, txtFirst4)
    Last = Array(txtLast1, txtLast2, txtLast3, txtLast4)
    x = 3
    If txtFirst4 = "" Then x = 2
    If txtFirst3 = "" Then x = 1
    If txtFirst2 = "" Then x = 0
    For i = 0 To x
        On Error Resume Next
        a = WorksheetFunction.Match(First(i) & Last(i), Range("L:L"), 0)
        If Err.Number <> 0 Then MsgBox (First(i) & " " & Last(i) _
            & " is not on the list")
        If Err.Number <> 0 Then GoTo one:
        If a > 0 Then MsgBox (First(i) & " " & Last(i) & _
            " is already on the list")
one:    Err.Clear
    Next i

 

[tusharm]

Re: Perform a two column lookup to check to see if a name ex

To look up one pair of textboxes, use

Option Explicit

Private Sub CommandButton1_Click()
    Dim RngOfInterest As Range, x As String
    Set RngOfInterest = Range(Range("d2"), Range("d2").End(xlDown))
    
    x = "SumProduct((" & RngOfInterest.Address & "= """ & Me.TextBox1.Text & """)" _
        & "*(" & RngOfInterest.Offset(0, 1).Address & "=""" & Me.TextBox2.Text _
        & """))"
    MsgBox Me.TextBox1.Text & " " & Me.TextBox2.Text & " " _
        & IIf(Evaluate(x) = 0, _
            "not found", "found")
    End Sub

Adapt it to your own columns of interest and your own userform names and number of textbox pairs.

 

[Mike in Phoenix]

Re: Perform a two column lookup to check to see if a name ex

Thanks for all the submittals. They helped a lot. What I ended up doing was using an Index and Match formula on a lookup sheet with a bunch of named ranges. From an input form, students are stored in the table and simultaneously on my lookup sheet but only the first and last names are stored on the lookup sheet in ranges First and Last and place a 1 in the Code range. The form to associate students with an event stores the info in the student_event table and also on the lookup table as NewFirst1 thru NewFirst4 and also NewLast1 thru NewLast4.
I created a lookup formula =INDEX(Code,MATCH(NewFirst1&NewLast1,First&Last,0)) but had problems with the error that is returned when no match was found so I used this formula to fix the problem =IF(ISERROR(O1),2,1). The lookup formula is of course placed in O1. I dragged down both formulas so that they could capture all four entries if they were present.
I wrote 4 SUBS that look like this
Sub NewStudent1()

ActiveWorkbook.Sheets("Lookup_Data").Activate
Dim Q As Variant

Q = Range("P1") ' This is the result of the ISERROR formula

If Q <> 1 Then 'Q only equals 1 if an exact match is found
MsgBox "The FIRST student you wanted to associate with this event is now associated but the student is not in the Students Table. You will now be redirected to the input form for the Students Table."
frmAssociateStudentEvent.Hide

frmStudent.txtFirst = frmAssociateStudentEvent.txtName1
frmStudent.txtLast = frmAssociateStudentEvent.txtName2
frmStudent.Show
End If


End Sub

Which are called from a click event
Private Sub cmdSubmit_Click()

Dim First, Last As Variant
ActiveWorkbook.Sheets("Event_Student").Activate

'Find last row with data and go to next row to input new data starting at row 4
'and column C
Range("a4").Select

Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If


Loop Until IsEmpty(ActiveCell) = True

'Place the form data in the correct column position in the worksheet
ActiveCell.Offset(0, 0) = txtEventName
ActiveCell.Offset(0, 1) = txtName1
ActiveCell.Offset(0, 2) = txtName2
ActiveCell.Offset(0, 3) = txtname3
ActiveCell.Offset(0, 4) = txtName4

ActiveWorkbook.Sheets("Lookup_Data").Activate


Range("NewFirst1").Select
Range("NewFirst1") = frmAssociateStudentEvent.txtName1


Range("NewLast1").Select
Range("NewLast1") = frmAssociateStudentEvent.txtName2

NewStudent1

ActiveWorkbook.Sheets("Event_Student").Activate

There is a similar for each of the four students possible entries but the text boxes and ranges are changed accordingly.

It works great! If you guys want to see more, I would be happy to provide more info.