Thanks Mike - but this was an example - I've actually got a whole heap

of "Type" so wanted a formula that picked up what was in column B

sheet 1 and look for it as a column heading in Sheet 2 rather than

type in the possible headings.

I've been trying with MATCH but not having any sucess. Have you got

any other suggestions?

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