IF Column A contains text in Column B then return corresponding number in C in Column D?

That extra detail makes it more convoluted, but now that I know you have Excel 2007/2010 we can simplify the formula, the two offsetting each other

Formula:

=IFERROR(INDEX(F$1:$F$40,MATCH(LEFT(A1,FIND(" v ",A1)-1),$D$1:$D$40,0)),

IFERROR(INDEX(F$1:$F$40,MATCH(MID(A1,FIND(" v ",A1)+3,FIND(" - ",A1)-FIND(" v ",A1)-3),$D$1:$D$40,0)),""))