I have a table (pasted below) showing COMPANY, BRAND, VALUE.
I am trying to put a fomula in E2 that looks at the company name in E1, then looks in the table and picks the brand AND the value for ONLY the MAX for the company in E2.
So, E2 should come up with BRAND3: 30
F2 should come up with BRAND8: 25
I am trying to put a fomula in E2 that looks at the company name in E1, then looks in the table and picks the brand AND the value for ONLY the MAX for the company in E2.
So, E2 should come up with BRAND3: 30
F2 should come up with BRAND8: 25
Book1 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | COMPANY | BRAND | VALUE | COMPANY1 | COMPANY2 | COMPANY3 | COMPANY4 | COMPANY5 | |||
2 | Company1 | Brand1 | 15 | Brand name: value | Brand name: value | Brand name: value | Brand name: value | Brand name: value | |||
3 | Company1 | Brand2 | 12 | ||||||||
4 | Company1 | Brand3 | 30 | ||||||||
5 | Company1 | Brand4 | 26 | ||||||||
6 | Company2 | Brand5 | 5 | ||||||||
7 | Company2 | Brand6 | 21 | ||||||||
8 | Company2 | Brand7 | 19 | ||||||||
9 | Company2 | Brand8 | 25 | ||||||||
10 | Company3 | Brand10 | 22 | ||||||||
11 | Company3 | Brand11 | 24 | ||||||||
12 | Company3 | Brand12 | 29 | ||||||||
13 | Company3 | Brand13 | 27 | ||||||||
14 | Company4 | Brand14 | 16 | ||||||||
15 | Company4 | Brand15 | 3 | ||||||||
16 | Company4 | Brand16 | 21 | ||||||||
17 | Company4 | Brand17 | 7 | ||||||||
18 | Company5 | Brand19 | 5 | ||||||||
19 | Company5 | Brand20 | 26 | ||||||||
20 | Company5 | Brand21 | 28 | ||||||||
21 | Company5 | Brand22 | 14 | ||||||||
Sheet1 |