Double LOOKUP? Help please...

[mitchy]

Hi,

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

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

I am struggling however to lookup from both drop-downs (Opposition an
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. Se
my problem? I can only LOOKUP one of the drop-down menu's.

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


Thank you in advance!

Mitc

 

[Guest]

Hi Mitchy

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

Andy.

 

[Daniel CHEN]

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
=================================