Lookup with Multiple criteria

[coach.32]

I am having troubles getting my formulas correct in reference to looking up multiple criteria.
I have got to this stage whereby I will give an example and hope the smart people can show me the formula.
As per the attached sample, I would like to match up Columns A, B,C and F from sheet2 with columns a,b,c and d in Sheet1.
Once the formula finds a match I would like the value in Column E of Sheet 1 to be placed in Sheet 2's Column G.
Could someone please help me solve this query. Thank you in advance for any support you can offer.

[FDibbins]

To begin with, none of your data matches.

You have 10 on sheet1 and U10 on sheet2
you have South Waverley on sheet1 and South Waverley/Sandown on sheet2

This will work, IF you can get your data sorted out (at least the Teams).
1. on sheet1, F2, copied down...
=A2&B2&C2&D2
On sheet2 H2, copied down...
=A2&MID(B2,2,99)&C2&F2
then on sheet2 G2, copied down...
=INDEX(Sheet1!$E$2:$E$13,MATCH(Sheet2!H2,Sheet1!$F$2:$F$13,0),1)

[JBeaucaire]

In G2, copied down:

=INDEX(Sheet1!$E$2:$E$13, MATCH($A2&"-"&$B2&"-"&$C2&"-"&LEFT($F2, 8)&"*", INDEX(Sheet1!$A$2:$A$13&"-U"&Sheet1!$B$2:$B$13&"-"&Sheet1!$C$2:$C$13&"-"&Sheet1!$D$2:$D$13,0),0))

[IgorSharov]

Once the formula finds a match

The formula will never finds a match. On Sheet1 The Age column has a number (10) and on Sheet2 the same column has a string (U10).

[JBeaucaire]

Each of the solutions given above, Igor, show ways around that inconsistency.