Lookup problem

[ashish514]

I want my formula to look up two values in two different columns. Refer to the attached link

http://www.box.net/shared/yed7167g9z

The blank columns in sheet 2 with headins in red are the ones needed to be filled by the formula which first looks up the family no. and then the family member no. in sheet 1 and then give the values of sex and age of those particular members in sheet 2. I could have just copy pasted the columns from sheet 1 to 2 after sorting, but not all the family members in a family travel daily and so their travel data is missing which makes it hard to copy paste. Kindly help

 

[Sandeep Warrier]

Hi

Try

=INDEX(Sheet1!C$2:C$34,MATCH(1,(Sheet1!$A$2:$A$34=$A2)*(Sheet1!$B$2:$B$33=$B2),0))

confirmed with control+shift+enter instead of just enter.

Copy down and across in columns G & H in sheet2

 

[ashish514]

Thanks a ton Sandeep...