Alternative to VLOOKUP

[cflorackis]

Dear all,

i have the following table

Book1.xls
	A	B	C	D	E	F	G	H	I
1	Company	Director	Stake		Company	Ceo Name			stake of CEOs
2	Company1	John	8.87		Company1	John			8.87
3	Company1	George	3.45		Company2	Helen			2.8
4	Company1	Michael	8		Company3	Nick			22.5
5	Company1	Alex	6
6	Company2	Adam	2.25
7	Company2	George	3.5
8	Company2	Helen	2.8
9	Company3	David	5.6
10	Company3	Nick	22.5
11	Company3	Antony	14.9
12	Company3	Helen	17.9
Sheet1

Columns A, B and C report the company name, the director's name and the director' s stake, whereas column F reports the name of the chief executive officer ( CEO) of each company. I need a formula that gives automatically the stake of each CEO, as shown in column I (e.g. Helen who is a CEO in company 2 holds 2.8% of company's 2 shares). I managed to do it in two steps (e.g. using a vlookup function in the first step and then sorting the values in the second step) but I would rather prefer a formula that gives the number automatically. Any help would be greatly appreciated

Kind regards

c.

 

[fairwinds]

Hi,

Try:

=LOOKUP(2,1/(($A$2:$A$12=E2)*($B$2:$B$12=F2)),$C$2:$C$12)

in I2 and drag down.

 

[Andrew Poulsom]

Given that your entries are unique, try:

=SUMPRODUCT(--($A$1:$A$12=E2),--($B$1:$B$12=F2),$C$1:$C$12)