Lookup with multiple criteria... please help!
 

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

Lookup with multiple criteria... please help!
 

I'd use a pivot table for this.

"anna" wrote:

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

Lookup with multiple criteria... please help!
 

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(L 1,$A$1:$I$1,0)-1,100,1))*(L3=OFFSET(A3,0,MATCH(L1,$A$1:$I$1,0),10 0,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

"anna" wrote:

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

Lookup with multiple criteria... please help!
 

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

"Barb Reinhardt" wrote:

I'd use a pivot table for this.

"anna" wrote:

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