LOOKUP / Retrieving Specific Cell (Auditing data)

LOOKUP / Retrieving Specific Cell (Auditing data – Need help)

Not sure which formula to use and how to use it, but here is what I need help with:
I have two worksheets in one workbook and both sheets contain names of individuals. One sheet contains a date. I need to audit the worksheet 1 to ensue all individuals have completed a form and insert the date that it was signed (which worksheet 2 captures). Example A provides a snapshot of how my cells are set-up:

Example A:

Worksheet 1 (entire population)
Columns: A B C
Headers: Date Signed User Name Displayed Name

Sample Data (currently blank) Joe Smith Joe Smith
James Smith James Smith
Jo Jo Snail Joseph Snail

Worksheet 2 (completed forms as of 1-25-11)
Columns A B C
Headers Name Title Date Signed

Sample data Joe Smith Sr. VP 12/31/2010
Joseph Snail Programmer 12/15/2010


I am trying to do a VLOOKUP by comparing worksheet 1 and 2, and inserting the date signed in worksheet 1, but if there is no name in worksheet 2, then in worksheet 1 = false. See example B, as this is what I would like the end result to look like:

Example B:

Worksheet 1 (entire population)
Columns: A B C D
Headers: Completed Date Signed User Name Displayed Name

Data: Yes 12/31/2010 Joe Smith Joe Smith
No James Smith James Smith
Yes 12/15/2010 Jo Jo Snail Joseph Snail

Worksheet 2 (completed forms as of 1 25 11)
Columns: A B C
Headers: Name Title Date Signed

Sample data Joe Smith Sr. VP 12/31/2010
Joseph Snail Programmer 12/15/2010



Please let me know if you have any questions or need clarification. Thanks for your help.

It is hard to see from your example how exactly the sheets are set up, but if you simply want to return a blank if no matches are found, then try something like:



=IF(ISNUMBER(MATCH(C2,'Worksheet 2'!A:A,0)),VLOOKUP(C2,'Worksheet 2'!A:C,3,0),"")

adjust sheetname and references to suit actuals.

If still not right, post an actual workbook sample attachment and indicate your requirements.

When you reply , you should see a paperclip icon in the reply box, click it and browse and upload the workbook.

In A2 enter:

=IF(ISNUMBER(MATCH(C2,'Completed forms as of 1 25 11'!A:A,0)),"Yes","")

copied down

In B2 enter:

=IF(A2="Yes",INDEX('Completed forms as of 1 25 11'!C:C,MATCH(C2,'Completed forms as of 1 25 11'!A:A,0)),"")

copied down

You may have to format this column as Date.