get cell value if vlookup returns a specific data

Hi all,
I am setting up a schedule and kinda hit a bump.
1. In sheet 1 I have the actual schedule. If someone is working "AM", he'll look for his name and make the change in the appropriate date column.
2. In sheet 2 I want the data to be organized by date and if the co-worker is working either "AM" or "PM". I am not sure what formula to use to get the name from sheet 1.

Any help would be much appreciated. Test.xls

Thanks,
Amar.

Is the result your need？Do you need the attachment？

Sub 查找人名()
   Dim arr, brr, crr(100, 2)
   Dim num1&, num2&
   Dim i&, j&, k&, i1&, j1&
   Dim a$, b$
       num1 = Sheet1.Range("c65536").End(xlUp).Row
       num2 = Sheet1.Range("aa4").End(xlToLeft).Column
       arr = Sheet1.Range(Sheet1.Cells(4, 3), Sheet1.Cells(num1, num2))
       num1 = Sheet2.Range("b65536").End(xlUp).Row
       num2 = Sheet2.Range("aa2").End(xlToLeft).Column
       Sheet2.Range(Sheet2.Cells(3, 3), Sheet2.Cells(num1, num2)).ClearContents
       brr = Sheet2.Range(Sheet2.Cells(2, 2), Sheet2.Cells(num1, num2))
       k = 0
          For j = 2 To UBound(brr, 2)
              For i = 2 To UBound(brr)
                 For j1 = 2 To UBound(arr, 2)
                     If brr(i, 1) = arr(1, j1) Then
                        For i1 = 2 To UBound(arr)
                            If arr(i1, j1) = brr(1, j) Then
                               brr(i, j) = arr(i1, 1) & brr(i, j)
                            End If
                        Next i1
                     End If
                  Next j1
                Next i
            Next j
         Sheet2.Range(Sheet2.Cells(2, 2), Sheet2.Cells(num1, num2)) = brr
End Sub

You are right,,I don't know how to use “UCase”，I just Modify the content “AM” ->“am”

You are right,,I don't know how to use “UCase”，I just Modify the content “AM” ->“am”

thank you Roger Govier
The modified

Sub 查找人名()
   Dim arr, brr, crr(100, 2)
   Dim num1&, num2&
   Dim i&, j&, k&, i1&, j1&
   Dim a$, b$
       num1 = Sheet1.Range("c65536").End(xlUp).Row
       num2 = Sheet1.Range("aa4").End(xlToLeft).Column
       arr = Sheet1.Range(Sheet1.Cells(4, 3), Sheet1.Cells(num1, num2))
       num1 = Sheet2.Range("b65536").End(xlUp).Row
       num2 = Sheet2.Range("aa2").End(xlToLeft).Column
       Sheet2.Range(Sheet2.Cells(3, 3), Sheet2.Cells(num1, num2)).ClearContents
       brr = Sheet2.Range(Sheet2.Cells(2, 2), Sheet2.Cells(num1, num2))
       k = 0
          For j = 2 To UBound(brr, 2)
              For i = 2 To UBound(brr)
                 For j1 = 2 To UBound(arr, 2)
                     If UCase(brr(i, 1)) = UCase(arr(1, j1)) Then
                        For i1 = 2 To UBound(arr)
                            If UCase(arr(i1, j1)) = UCase(brr(1, j)) Then
                               brr(i, j) = arr(i1, 1) & brr(i, j)
                            End If
                        Next i1
                     End If
                  Next j1
                Next i
            Next j
         Sheet2.Range(Sheet2.Cells(2, 2), Sheet2.Cells(num1, num2)) = brr
End Sub

Hey guys,
After lots of trial and error, I finally figured out the formula. I didn't use vlookup. Here it is:

=IFERROR(INDEX(Sheet1!$B$4:$B$20,MATCH(Sheet2!$A$6,Sheet1!C$4:C$20,0)),"")

For those who need it, here's an explanation of what it does.

1. Cells B4:B20 in sheet1, contains the names of my co-workers
2. Cell A6 in sheet 2 contains "AM".
3. Cells C4:C20 in sheet 1, is the row that the formula needs to "look in" and it has a date in cell C4.

If someone is working "AM" on 12/06/2013, the formula will find the corresponding name in cell B4:20 and drop it in sheet 2.

Thanks for your response.

Regards,
Amar.

Hi,
I mentioned that I had to use a combination of formulas and do a lot of research.
I'm not sure how it is different from what I posted/requested. I've attached a sample spreadsheet.

Regards.

Just chill man. I did thank them for their input. I don't know why you're making such a fuss?!
I forgot to unhide it. it happens. Human error!
No data in my second workbook and didn't solve the problem? What are you talking about?
I was only looking for help and as I was waiting for an answer, I was also trying to solve my problem... which I did.
I don't want you to think that I'm a "cocky" user, but if you don't want to help me, then so be it.

Have a great day.