What Function Do I Need

[Peter100]

Hi

In A3 to B7 I have data as shown below, I want to display in E4 the minimum value of B3:B7 and in D4 the corresponding Option reference ie. A,B,C etc.

I cant lay the table out the other way round so as I could use a simple lookup.
Can anyone help please

Book1
	A	B	C	D	E
2	Option	Value		MinimumValue
3	A	75		OptionNo	Value
4	B	22
5	C	67
6	D	45
7	E	12
8	F	99
Sheet1

 

[Scott Huish]

Will there ever be duplicate minimum values?

 

[Kjuel]

Try

C3=A3
C4=A4
...
C7=A7
(hide the column C)

E4 = MIN(B3:B7)
D4 = VLOOKUP(E4,B3:C7,2,FALSE)

 

[Scott Huish]

If there are no duplicates, you don't need a helper column.

formula in E3:
=MIN(B:B)

Formula in D3:
=INDEX(A:A,MATCH(E3,B:B,0))

 

[Peter100]

so unlikely I will say no

But youve raised an interesting point.
Would there be a way to indicate that two values were equal minimum ?

But lets leave that until later

 

[Peter100]

Hi Hotpepper

It displayed the min Ok but put the text "option in D3. As index & match were on the column in total, I set the ranges to be A3:A8 & B3:B8 in both and this seems to work.

MANY THANKS

Index & Match I have yet to get to grips with fully.

Any ideas if there was a duplicate min value ?

 

[Peter_SSs]

Peter100

If I have understood correctly, here is one way of dealing with multiple minimums.

1. E4: =MIN(B3:B8)
2. F4: =COUNTIF(B3:B8,E4)
3. C3 (copied down): =IF(B3=$E$4,COUNTIF(B$3:B3,$E$4),"")
4. D4 (copied down): =IF(ROWS(D$4:D4)<=F$4,INDEX($A$3:$A$8,MATCH(ROWS(D$4:D4),$C$3:$C$8,0)),"")

Mr Excel.xls
	A	B	C	D	E	F	G
1
2	Option	Value		Minimum Value
3	A	75		Option No	Value	Occurrances
4	B	22		C	12	2
5	C	12	1	E
6	D	45
7	E	12	2
8	F	99
9
Minimums