Lookup with multiple criteria... please help!

[Guest]

I have a worksheet with a list of results, such as...

Michael Tom Sue
etc
Num1 Num2 Results Num1 Num2 Results Num1 Num2 Results
1 0 5 1 0 7
2 0 10 2 0 11
.... ... ... ... ... ...
10 0 25 10 0 24
1 1 6 1 1 9
2 1 12 2 1 14
.... ... ... ... ... ...
10 1 27 10 1 26

Hopefully, you get the idea. On another worksheet, I need to display the
results given the "name" and "num1" and "num2". The array references change
with the "name" chosen AND there are two search criteria per name. Could
anyone please help me out with the correct formula for this?
Thanks so much!
anna

 

[Guest]

I'd use a pivot table for this.

 

[Guest]

Anna,
Hopefully this will point you in the right direction. There
may be a smarter way to do this so hopefully one of the "Formulae" gurus will
respond.

My data was cells A1 to I100 for the purpose of this test.

Row 1 contained the names(Michael,Tom, Sue) in every third column (A,D,G)
and row 2 contained the repeated headings "Num1","Num2","Result". Values of
"Num1" etc start in row 3

Cell L1 has the <Name>, L2 the value of <Num1> and L3 the value of <Num2>

The formula below is entered as an array formula using CtrlL+Shift + Enter

=INDEX($A$3:$I$100,MATCH(1,(L2=OFFSET(A3,0,MATCH(L1,$A$1:$I$1,0)-1,100,1))*(L3=OFFSET(A3,0,MATCH(L1,$A$1:$I$1,0),100,1)),0),MATCH(L1,$A$1:$I$1,0)+2)


It allows for 100 rows of data but this can easily be changed.

You will have to qualify the various ranges sheet name or perhas better make
them named ranges. Note also it doesn't cater for error condtions which can
be handled using:

=IF(ISNA(Formula),"",Formula) to return a blank if an error occurs.

HTH

 

[Guest]

could you elaborate please? I am not familiar with this.
Thanks,
Anna