Try this.

=VLOOKUP(A2,Sheet2!$A$1:$G$5,VLOOKUP(B2,{"child",7 ;"woman",5;"man",3},2,FALSE),FALSE)

Mike

wrote in message
...
Hello

Can you please help me with the following.

I need to lookup up a value in the cell A1 of sheet 1 and return the
value in sheet 2 of the column next to the the column with a heading
matching the value of B2 in sheet 1.

For example.

Sheet 1

Ref Type
1 Man
2 Woman
3 Man
4 Child

Sheet 2

Ref Man Man type Woman Woman type Child Child type
1 Peter x Sarah y Lily
x
2 John y Jane z Max x
3 Bob y Jane x Alice
x
4 Fred z Jane x Ruby
y

So I would expect the returned value on sheet 1 to be:

Ref Type Result
1 Man x
2 Woman z
3 Man y
4 Child y

Any help on this one greatly appreciated