You could change the inner vlookup and have it reference a helper table.
The helper table would be a list of the heading names you want use and the
column number of the heading.

It would look something like this

=VLOOKUP(A2,Sheet2!$A$1:$G$5,VLOOKUP(B2,TypeList,2,FALSE),FALSE)

where TypeList would be

Type    Column
Man        3
Woman    5
Child        7
Other        9
etc.

Part of the problem is the data isn't oganized very well.  Can you
reorganize your data? If so it would be easier to work with it if you could
set it up like this:

Ref    Name    Type    Value
1        Peter    Man           x
2        John      Man          y
1        Sue      Woman      x
1        Alice    Child          x
5        Mark    Man          z

then you could use a whole host of methods to get your data.  Such as array
formulas, sumproduct, pivot tables, the query.
For example to use formulas, you could either create range names or
reference the cells directly.  With range names in sumproduct it would look
like this:

Ref    Type    Result
1        Man      =sumproduct(--(Ref=$A2),--(Type=$B2), Value))

with cell reference it would be

Ref    Type    Result
1        Man      =sumproduct(--($A$2:$A$500=$A2),--($B$2:$B$500=$B2),
$C$2:$C$500))

or with array formulas it would be

Ref    Type    Result
1        Man        = SUM(IF(Ref=$A2),IF(Type=$B2,Value))  then press CTRL
SHIFT ENTER while still in the formula bar.

Mike