VLookup with multiple conditions?

[Ajfmaizy]

Hi all,

I'm stuck on something that I imagine there's a fix for - grateful for help.

I'm doing analysis of a load of exam results - they are all in one huge sheet. I want to separate them into category based on Year, Topic, Gender (so for example I want a list of 2012 Maths Girls, 2013 Art Boys, etc). There are 30 or so topics.

So far, I've been using a filter on the main sheet for these three things, copying the marks across to a new sheet, then doing an average/making a graph/etc. But I realise that with 30 topics, this will take a long time.

I'm wanting a formula that goes through row-by-row and says 'IF Boy, IF Maths, IF 2012, then copy the value in row R across'.
VLookUp I've used before, but that (AFAIK) can only do it based on one of these IFs, not multiple. (I could run it three times in sequence, but that's a lot of iterations and manual effort to get the result).
Another option I've thought of is to make a list of all the possible marks and do a SUMPRODUCT, which would tell me how many of each there are (but then it's harder to do an average). Is this my best option, or is there some magic that can do what I'm after?

Thanks in advance
AJ

[Shareez Saleem]

Can you upload a sample sheet with the expected result?

[Ajfmaizy]

++ Post removed in retrospect because I am a forum noob who didn't upload properly ++

[Shareez Saleem]

I cant access to your file

[Ajfmaizy]

Here's a sample I mocked up

(This isn't the actual data I'm working with but should do the same job for help)

[Shareez Saleem]

Please refer the sheet and try to do the same.

[perpectuals]

Wow... Great. I know this learning today - Slicer. Thanks Mr.Shareez!

[FDibbins]

Shareez Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[Shareez Saleem]

Noted Dibbins.

I will try to explain going forward.

[Ajfmaizy]

Thanks Shareez - I'll take a look at it when I'm back in work next week (I haven't got excel on my laptop)