Help with Excel (1 Viewer)

[bladeys]

Is anyone here a wizard at excel?

Here is my hypothetical scenario, I had a table with 3 columns with the headings: Suburb, Sex and Age
Lets say I list 5 suburbs, and for each suburb I have a male and a female of different ages so there will be 10 rows underneath my 3 column headings.

How do I utilise a formula so that I can look up their age of a male who lives in Manly for example? Like I want the conditions that satisfy to be that the person lives in Manly (so two possibilities) but then is also male (therefore reducing to one possibility) which then should give me the age of that individual

Could anyone please help =)

 

[kaz1]

look up filters on excel help

also this would be better done on access imo

 

[bladeys]

Ah right, apparently its gotta be done on excel though

And my example was probably a bit too simple and inaccurate, I am trying to extract the data (age) on a different sheet to the one that has the table so that kinda rules out the filter solution

thanks for the quick response btw =)

 

[soloooooo]

Vlookup and hlookup.

 

[bladeys]

Vlookup and hlookup.

How do you combine vlookups though, because I want the condition for the suburb to satisfy and the sex

 

[halapenyo]

is this for a summer course?

 

[bladeys]

is this for a summer course?

nah its for my job =/

 

[Arcorn]

This sort of seems like someone who is making a list of people to kidnap in different suburbs and ensuring they are a certain age to create a pattern.

 

[bladeys]

This sort of seems like someone who is making a list of people to kidnap in different suburbs and ensuring they are a certain age to create a pattern.

HAHAHAH

would you be able to help me out with that then?

 

[townie]

Assume the data is in A1:C11 (with headings)

Then in columns E,F and G create headings suburb, sex and age

To search put the suburb you want in E2 the Sex you want in F2 then in G2 put the following formula

=INDEX(A2:C11,MATCH(E2&F2,A2:A11&B2:B11,0),3)

Then instead of hitting enter at the end of the formula hit CTRL + SHIFT + ENTER (for an array formula, you should end up with { brackets around the formula

 

[bladeys]

cheers townie!

Just a couple of questions,

What does the 3 at the very end do?
and what exactly does an array formula do exactly? Someone showed me how to create an array formula but they didnt really have time to explain what it did, they just told me to do it