Formula/function?

[number cruncher]

I'm sure this is a simple thing but m excel skills are somewhat limited. Hoping someone can help.

I have 2 columns of data in sheet 1 under columns 'a' (titled 'Females') and 'b' (titled 'Male').
I have the same list of data but all in column 'a' of sheet 2 (listed in a different order than sheet 1).
In sheet 2, column b, if I want to insert which column (Males or females in sheet 1) that cell matches then what formula should I be using? NB, the cell value can appear in both columns a & b of sheet 1.

Thanks!!

 

[Lewiy]

I have put it all on one sheet for display purposes, but does this help?:

Book4
	A	B	C	D	E	F	G
1	Female	Male		Names	Column
2	Sarah	John		Bill	Male
3	Anne	Bill		Anne	Female
4	Sue	Joe		Sue	Female
5				John	Male
6				Joe	Male
7				Sarah	Female
8
9
10
11
12
13
14
15
Sheet1

 

[number cruncher]

Thank you sooooooooooooo much!! - this works like a charm, the only problem is that if the item appears in both columns, it picks up on the first column only. How do I add a "both" result to the formula?