Formula/vba to search partial strings in other sheet

All,

I am having names in two cells of sheet1 (e.g. : A1 (Gina Williams) & A2 (Patrick Rafter)) and the corresponding bank statement narratives are in sheet 2 (column B) e.g: "Deposit from Gina towards rent for connaught place apt".

Now i need to search all the four partial texts available in cells A1 & A2 of sheet 1 (ie. "Gina", "Williams", "Patrick", "Rafter" in the entire column B of sheet 2. if there is a match i need to capture the corresponding column A & C value for the matching row.

SHEET1
A | B | C
1 | GINA WILLIAMS | OUTPUT (matching col A of sheet2) | OUTPUT (matching col C of sheet2)
2 | PATRICK RAFTER | OUTPUT (matching col A of sheet2) | OUTPUT (matching col C of sheet2)

SHEET2
A | B | C
1.| 12/7/2015 | Deposit from Gina towards rent for connaught place apt | 320
2. |13/7/2015 | Deposit from Rafter towards rent for connaught place apt | 700


kindly help me to fill the formulas for both col b & c of sheet1

Thanks.

Hi there. Copy this across and down

Formula:

=IFERROR(IFERROR(INDEX(Sheet2!B:B,MATCH("*"&LEFT($A2,FIND(" ",$A2)-1)&"*",Sheet2!$B$1:$B$20,0)),INDEX(Sheet2!B:B,MATCH("*"&MID($A2,FIND(" ",$A2)+1,255)&"*",Sheet2!$B$1:$B$20,0))),"")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.