Finding adjacent cell of min/max formula?

[Elihue]

Hi,

I need to find the adjacent cell of a min/max value and return it. However, I can't seem to make this work for my particular situation. I have two tables on separate tabs. One with all my data, and one I want to fill with specific data from the first table.

Table #1 structure example

ObjectID	1 F Comm Own.	1 F Comm Ht.	2 F Comm Own.	2 F Comm Ht.	other data	other data	other data	1 B Comm Own.	1 B Comm Ht.	2 B Comm Own.	2 B Comm Ht.	other data	other data
1
2
3
4
5
6
7
8
9
10
ect...

<tbody>
</tbody>

I am using this formula to fill the Ht. columns on table #2 . It looks at "1 F Comm Ht." & "1 B Comm Ht." and puts the max value in "1 Highest Comm Ht."
=IF(MAX('Pole Data'!CU3,'Pole Data'!IA3)=0,"",MAX('Pole Data'!CU3,'Pole Data'!IA3))

I'm looking for a formula that will put the Owner of the max value in the adjacent cell called "1 Highest Comm Own."

table #2 structure example.

ObjectID	Highest MISC. Comm	1 Highest Comm Own.	1 Highest Comm Ht.	2 Highest Comm Own.	2 Highest Comm Ht.	other data	other data	other data	other data	other data	other data	other data
1
2
3
4
5
6
7
8
9
10
ect...

<tbody>
</tbody>

 

[Elihue]

Something like this: =INDEX('Pole Data'!CT3,'Pole Data'!HZ3,MATCH(MAX('Pole Data'!CU3,'Pole Data'!IA3),'Pole Data'!CU3,'Pole Data'!IA3,0)) gives me an error saying "Too many arguements"

 

[Eric W]

I'm having a devil of a time trying to figure out what you want. You showed your layout, roughly, but didn't include column/row headers or any sample data and desired results. Here's the best I can figure:

	CS	CT	CU	CV	CW	CX	CY	CZ	DA	HY	HZ	IA	IB	IC	ID	IE
1	ObjectID	1 F Comm Own.	1 F Comm Ht.	2 F Comm Own.	2 F Comm Ht.	other data	other data	other data			1 B Comm Own.	1 B Comm Ht.	2 B Comm Own.	2 B Comm Ht.	other data	other data
2	1	Andy	10			A11	A22	A33			Alyce	9	AA11	AA22	AA33
3	2	Bob	12			B11	B22	B33			Beyonce	11	BB11	BB22	BB33
4	3	Cal	14			C11	C22	C33			Cyndi	13	CC11	CC22	CC33
5	4	Doug	11			D11	D22	D33			Debra	15	DD11	DD22	DD33
6	5	Eddy	10			E11	E22	E33			Elayne	12	EE11	EE22	EE33
7	6	Frank	6			F11	F22	F33			Felice	6	FF11	FF22	FF33
8

<tbody> </tbody>

Pole Data​

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ObjectID	Highest MISC. Comm	1 Highest Comm Own.	1 Highest Comm Ht.	2 Highest Comm Own.	2 Highest Comm Ht.	other data	other data	other data	other data	other data	other data	other data
2			Debra	15	Cal	14	??	??	??

<tbody> </tbody>

Sheet3​

Worksheet Formulas Cell Formula C2 =IFERROR(INDEX('Pole Data'!$CT$2:$CT$10,MATCH(D2,'Pole Data'!$CU$2:$CU$10,0)),INDEX('Pole Data'!$HZ$2:$HZ$10,MATCH(D2,'Pole Data'!$IA$2:$IA$10,0))) D2 =MAX('Pole Data'!CU:CU,'Pole Data'!IA:IA) E2 =IFERROR(INDEX('Pole Data'!$CT$2:$CT$10,MATCH(F2,'Pole Data'!$CU$2:$CU$10,0)),INDEX('Pole Data'!$HZ$2:$HZ$10,MATCH(F2,'Pole Data'!$IA$2:$IA$10,0))) F2 =LARGE(('Pole Data'!CU:CU,'Pole Data'!IA:IA),2) <tbody> </tbody>

<tbody> </tbody>

The formulas work, but they are just examples. They don't copy/paste the way you'd want, nor do they handle duplicates. I can adjust them to do both, but before I spend more time on this, please explain exactly what your tables look like, and exactly what results you want. Do you want more than one result row? More data in the "other data" columns?