**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.