Double LOOKUP? Help please...
 

Hi,

Firstly, let me mention that i am relativley unfamiliar with excel :(

My current project is based around the analysis of soccer player
statistics. I have a worksheet (lets call it worksheet 1) which has a
drop down menu where you can select the player, and then another
drop-down menu where you select the opposition. The stastics of each
player are kept in a table on another worksheet (lets call it worksheet
2), along with the name of the opposition. I would like worksheet 1 to
look up all of the statistics kept in the table of worksheet 2.

I am struggling however to lookup from both drop-downs (Opposition and
Player) I can only work out how to do a LOOKUP for one...

My current lookup formula is:
=LOOKUP(L6, Data_Sheet!K11:N11,Data_Sheet!K14:N14)

Here,
L6: Player Number
Data_Sheet!K11:N11: Opposition Number
Data_Sheet!K14:N14: Statistics

This however will only look up the opposition and not the player. See
my problem? I can only LOOKUP one of the drop-down menu's.

I can provide screenshots, or the actual file if such a large
attatchment is allowed.


Thank you in advance!

Mitch


--
mitchy
------------------------------------------------------------------------
mitchy's Profile: http://www.excelforum.com/member.php...o&userid=32605
View this thread: http://www.excelforum.com/showthread...hreadid=524039

Double LOOKUP? Help please...
 

Hi Mitchy

It sounds like an ideal job for SUMPRODUCT. It would help if you could post
a bit more info.

Andy.

"mitchy" wrote in
message ...

Hi,

Firstly, let me mention that i am relativley unfamiliar with excel :(

My current project is based around the analysis of soccer player
statistics. I have a worksheet (lets call it worksheet 1) which has a
drop down menu where you can select the player, and then another
drop-down menu where you select the opposition. The stastics of each
player are kept in a table on another worksheet (lets call it worksheet
2), along with the name of the opposition. I would like worksheet 1 to
look up all of the statistics kept in the table of worksheet 2.

I am struggling however to lookup from both drop-downs (Opposition and
Player) I can only work out how to do a LOOKUP for one...

My current lookup formula is:
=LOOKUP(L6, Data_Sheet!K11:N11,Data_Sheet!K14:N14)

Here,
L6: Player Number
Data_Sheet!K11:N11: Opposition Number
Data_Sheet!K14:N14: Statistics

This however will only look up the opposition and not the player. See
my problem? I can only LOOKUP one of the drop-down menu's.

I can provide screenshots, or the actual file if such a large
attatchment is allowed.


Thank you in advance!

Mitch


--
mitchy
------------------------------------------------------------------------
mitchy's Profile:
http://www.excelforum.com/member.php...o&userid=32605
View this thread: http://www.excelforum.com/showthread...hreadid=524039

Double LOOKUP? Help please...
 

If your statistics are all numbers, then the following array formula may
help:

=AVERAGE(IF(((Sheet1!$A$2:$A$99)="Pos")*((Sheet1!$ B$2:$B$99)="Nam"),Sheet1!$C$2:$C$99))

Assuming Sheet1 Column A has position and Column B has Name and the
statistics are in column C.
"Pos" and "Nam" can be from your dropdown list.

You need to use Ctrl+Shift+Enter, rather than Enter key, after typing the
formula.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
=================================


"mitchy" wrote in
message ...

Hi,

Firstly, let me mention that i am relativley unfamiliar with excel :(

My current project is based around the analysis of soccer player
statistics. I have a worksheet (lets call it worksheet 1) which has a
drop down menu where you can select the player, and then another
drop-down menu where you select the opposition. The stastics of each
player are kept in a table on another worksheet (lets call it worksheet
2), along with the name of the opposition. I would like worksheet 1 to
look up all of the statistics kept in the table of worksheet 2.

I am struggling however to lookup from both drop-downs (Opposition and
Player) I can only work out how to do a LOOKUP for one...

My current lookup formula is:
=LOOKUP(L6, Data_Sheet!K11:N11,Data_Sheet!K14:N14)

Here,
L6: Player Number
Data_Sheet!K11:N11: Opposition Number
Data_Sheet!K14:N14: Statistics

This however will only look up the opposition and not the player. See
my problem? I can only LOOKUP one of the drop-down menu's.

I can provide screenshots, or the actual file if such a large
attatchment is allowed.


Thank you in advance!

Mitch


--
mitchy
------------------------------------------------------------------------
mitchy's Profile:
http://www.excelforum.com/member.php...o&userid=32605
View this thread: http://www.excelforum.com/showthread...hreadid=524039