Need Help with Searching for Two Words

I have two columns in one worksheet containing a first name and a last name:
WK1ColA WK1ColB
Billy Bob
John Doe
Jane Smith

In the same file but another worksheet I have two more columns one with the full name and another with a date:
WK2ColA WK2ColB
Billy Bob 1/15/2004
John Doe 6/21/2004
Jane Smith 7/1/2004

I want to search the entire ColA2 to find the cell that has both ColA1 and ColB1 and then return the corresponding ColB2 value for that name. None of the columns can be sorted alphabetically or numerically. I was hoping that something like this would work but it returns a general error stating that the function is wrong which I believe that the "AND" is causing the error:

=OFFSET('WorkSheet2'!$A$1, MATCH ('WorkSheet1'!A1 AND 'WorkSheet1'!B1, 'WorkSheet2'!$A$2:$C$500, 0), 1)

I am desperate for help and would appreciate any input anyone can provide. Thank you.

Originally Posted by Dominator

I have two columns in one worksheet containing a first name and a last name:
WK1ColA WK1ColB
Billy Bob
John Doe
Jane Smith

In the same file but another worksheet I have two more columns one with the full name and another with a date:
WK2ColA WK2ColB
Billy Bob 1/15/2004
John Doe 6/21/2004
Jane Smith 7/1/2004

I want to search the entire ColA2 to find the cell that has both ColA1 and ColB1 and then return the corresponding ColB2 value for that name. None of the columns can be sorted alphabetically or numerically. I was hoping that something like this would work but it returns a general error stating that the function is wrong which I believe that the "AND" is causing the error:

=OFFSET('WorkSheet2'!$A$1, MATCH ('WorkSheet1'!A1 AND 'WorkSheet1'!B1, 'WorkSheet2'!$A$2:$C$500, 0), 1)

I am desperate for help and would appreciate any input anyone can provide. Thank you.

Hello Dominator,

Match doesn't accept expressions for the Lookup Value (1st argument) only a number, text, logical value or a reference to a Single Cell. You should combine Worksheet A1 & Worksheet B1 into another cell like Worksheet C1. The Formula would then look like...
________________________________________________________________

=OFFSET(Sheet2!A1,MATCH(Sheet1!C1, Sheet2!A1:A500, 0), 1)
________________________________________________________________

Hope this helps,
Leith Ross