Formula to find the location of a value in a range

[woodsy74]

Below is a sample of the data set (starting in cell A1) I am working with (the real set has many more rows & columns). I have a formula which will return me the Min value (-2.2) and the Max value (3.0). What I am trying to do is add a formula that will return me the location of where the Min and Max values are located. I would like to return either F6 or NY for the Min value and either E3 or GA for the Max value -- looking to get either the cell location or the column header of where the value is located. It is possible that the Min and Max values will be found in multiple locations. I am only concerned with showing one location (it could be the first or the last it doesn't matter).

Is this something that can be done via a formula? Thanks.

	CA	TX	FL	GA	NY	NJ
Ap	1.2	2.2	0.2	2.7	-0.3	1.2
Or	1.5	2.5	0.5	3.0	0	1.5
Ba	1.1	2.1	0.1	2.6	-0.4	1.1
Pe	0	1.0	-1.0	1.5	-1.5	0
Ch	-0.7	0.3	-1.7	0.8	-2.2	-0.7
Gr	1.0	2.0	0	2.5	-0.5	1.0

<tbody>
</tbody>

 

[tyija1995]

Hey,

For the data given try these two formulas:

Put -2.2 in cell I1 and 3 in cell J1 then:

I2:

ADDRESS(SUMPRODUCT(($B$2:$G$7=I1)*ROW($B$2:$G$7)),SUMPRODUCT(($B$2:$G$7=I1)*COLUMN($B$2:$G$7)))

Drag that across to J2.

Change the range to the appropriate range for your entire data set and you should be good to go!

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J
1		CA	TX	FL	GA	NY	NJ		-2.2	3
2	Ap	1.2	2.2	0.2	2.7	-0.3	1.2
3	Or	1.5	2.5	0.5	3	0	1.5		NY	TX
4	Ba	1.1	2.1	0.1	2.6	-0.4	1.1
5	Pe	0	1	-1	1.5	-1.5	0
6	Ch	-0.7	3	-1.7	0.8	-2.2	-0.7
7	Gr	1	2	0	2.5	-0.5	1
Sheet2

Cell Formulas
Range	Formula
I3	=INDEX($B$1:$G$1,AGGREGATE(15,6,(COLUMN($B$2:$G$7)-COLUMN($B$2)+1)/($B$2:$G$7=I1),1))
J3	=INDEX($B$1:$G$1,AGGREGATE(15,6,(COLUMN($B$2:$G$7)-COLUMN($B$2)+1)/($B$2:$G$7=J1),1))

@tyija1995
Your formula will fail if there are duplicates.

 

[woodsy74]

This worked great until I started changing some of the values within my data set. Before I made changes the Min formula correctly returned $F$6. I then went into cell A2 and entered -2.2 and when I did the Min formula incorrectly returned $K$11 (which is outside my data set). The formula was working with other changes but doesn't seem to work if the Min (or Max) value is listed more than once in the data set. Is there a way around this? I don't care which cell location I receive from the formula as long as it matches the value I am looking for.

 

[woodsy74]

Sorry, I didn't see Fluff's reply. So far that updated formula seems to be working returning the column header. What does the 15,6 represent? If my full data set is B2:HV42 will this work?
Thanks!

 

[Fluff]

You're welcome & thanks for the feedback