Multiple Lookup Query

[dominicwellsuk]

Hi, I'm trying to set up a sheet of data from which I can lookup all values against a particular name, regardless of where they are in the list, and there will multiple lines for each name. I think pivot tables woud do the job, but for various reasons they're not suitable.

Imagine this golfing example for scores on each hole...

Name Course 1st 2nd 3rd 4th 5th etc.....
J.Smith Peabeck 4 5 3 5 4
J.Doe Quagmire 3 4 4 4 6
J.Smith Peabeck 3 5 3 4 5
J.Doe Peabeck 4 4 3 6 3
J.Smith Quagmire 5 5 4 4 4

Is there a way of calculating for example, how many scores of "4" J.Doe has got on hole 2 at "Quagmire", or getting an average number of shots for J.Smith on hole 5 at Peabeck? (Names have been changed to protect the innocent).

This might be a really simple problem, but my mind is failing me. Any creative types out there without pre-bank holiday concentration levels?

Thanks

 

[dominicwellsuk]

P.S. Sorry for the formatting in the visual example. I hope it's clear

 

[T. Valko]

Hi, I'm trying to set up a sheet of data from which I can lookup all values against a particular name, regardless of where they are in the list, and there will multiple lines for each name. I think pivot tables woud do the job, but for various reasons they're not suitable.

Imagine this golfing example for scores on each hole...

Name Course 1st 2nd 3rd 4th 5th etc.....
J.Smith Peabeck 4 5 3 5 4
J.Doe Quagmire 3 4 4 4 6
J.Smith Peabeck 3 5 3 4 5
J.Doe Peabeck 4 4 3 6 3
J.Smith Quagmire 5 5 4 4 4

Is there a way of calculating for example, how many scores of "4" J.Doe has got on hole 2 at "Quagmire", or getting an average number of shots for J.Smith on hole 5 at Peabeck? (Names have been changed to protect the innocent).

This might be a really simple problem, but my mind is failing me. Any creative types out there without pre-bank holiday concentration levels?

Thanks

What version of Excel are you using?

Also, are the hole numbers really ordinal numbers: 1st 2nd 3rd 4th 5th etc.....

Or, are they: 1 2 3 4 5 etc...

 

[dominicwellsuk]

Sorry, should have said, I'm using 2003, and the hole numbers are formatted as "1st", "2nd" etc at the moment, but that's not critical. They can be 1,2,3 etc.

 

[T. Valko]

Sorry, should have said, I'm using 2003, and the hole numbers are formatted as "1st", "2nd" etc at the moment, but that's not critical. They can be 1,2,3 etc.

OK, with your data as such:

Book1
	A	B	C	D	E	F	G
1	Name	Course	1	2	3	4	5
2	J.Smith	Peabeck	4	5	3	5	4
3	J.Doe	Quagmire	3	4	4	4	6
4	J.Smith	Peabeck	3	5	3	4	5
5	J.Doe	Peabeck	4	4	3	6	3
6	J.Smith	Quagmire	5	5	4	4	4
Sheet1

average number of shots for J.Smith on hole 5 at Peabeck?

Use cells to hold the criteria:

• A11 = J.Smith
• B11 = Peabeck
• C11 = 5

Array entered**:

=AVERAGE(IF(A2:A6=A11,IF(B2:B6=B11,INDEX(C2:G6,,C11))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

how many scores of "4" J.Doe has got on hole 2 at "Quagmire"

• A11 = J.Doe
• B11 = Quagmire
• C11 = 2
• D11 = 4

=SUMPRODUCT(--(A2:A6=A11),--(B2:B6=B11),--(INDEX(C2:G6,,C11)=D11))

 

[dominicwellsuk]

Exquisite Excelmanship sir.

This might be obvious after what you've said so sorry, but for the last example, if I wanted to remove the constraint of a particular course and particular hole, how would that be shown in the formula?

Thanks.