Returning data in adjacent cells after MIN function

[perdactyre]

Hi, i have a list of names and scores like this.

dave 4 3
tim 6 8
rob 10 4
mike 1 11

X Y

basically what i want to do is in the X and Y cells is have a min funtion for the respective column which returns the name of the person rather than the actual number. Ive played with it some but cant get it to work.

Thanks for your help

Roger

 

[PaddyD]

hi - welcome to the board!

see if this helps:

WeddingAddresses_update.xls
	A	B	C	D	E
1	Name	Num1	Num2
2	Dave	4	3
3	Tim	6	8
4	Rob	10	4
5	Mike	1	11
6		Mike	Dave
7
Sheet2

the formula is:

=INDEX($A$2:$A$5,MATCH(MIN(B2:B5),B2:B5,0),1)

...although you'll need something a little more elaborate if you're likely to have more than one person scoring the minimum value.

 

[Lokai]

Following on Paddy's solution...
if you are looking for the smallest value (min) you can also use:
=INDEX(A2:A5,MATCH(SMALL(B2:B5,1),B2:B5,0),1)
if you are looking for the 2nd smallest value then: =INDEX(A2:A5,MATCH(SMALL(B2:B5,2),B2:B5,0),1) etc...

 

[perdactyre]

thanks, thats exactly what i want. you even read my mind on the SMALL function that i was palnning on using, just didn't want to over complicate the question. And luckily enough the numbers are based on the finish order of a race, so there can only be one min or max.

Thanks again.

 

[perdactyre]

I got it to work, but am wondering how exactly does this formula function?
What Is the Index function doing and what is the Match function doing. The help file is about as clear as mud.

Im just the type that wonders about this kind of thing.

Thanks again.

 

[perdactyre]

Sorry to drag this on even further, but now id like to find the average of the entire row from wich the min value of the coloumn was found. Ie


tot name #1 #2
3 dave 1 2
7 tim 2 5
13 rob 5 7
6 gary 4 2
16 joe 8 8

now that i can get "dave" to return as the guy with the lowest score, id like to do an average of daves scores. the output would look somthing like this

Lowest avg score
dave 1.5

its prob simmilar to the help that was already given, but like i said i dont fully understand how that funtion is working.

Thanks again

 

[Aladin Akyurek]

...And luckily enough the numbers are based on the finish order of a race, so there can only be one min or max...

Are you that certain? Why "luckily"? It's not an issue (anymore) that there can be more than one min or max for that matter.

 

[Yogi Anand]

Hi perdactyre:

Let us have a look at ...

Book4
	A	B	C	D
1	tot	name	#1	#2
2	3	dave	1	2
3	7	tim	2	5
4	13	rob	5	7
5	6	gary	4	2
6	16	joe	8	8
7		Lowest Score	dave	tim
8		average	1.5	3.5
Sheet13

Is this what you are looking for?

 

[Aladin Akyurek]

Sorry to drag this on even further, but now id like to find the average of the entire row from wich the min value of the coloumn was found. Ie


tot name #1 #2
3 dave 1 2
7 tim 2 5
13 rob 5 7
6 gary 4 2
16 joe 8 8

now that i can get "dave" to return as the guy with the lowest score, id like to do an average of daves scores. the output would look somthing like this

Lowest avg score
dave 1.5

its prob simmilar to the help that was already given, but like i said i dont fully understand how that funtion is working.

Thanks again

Book1
	A	B	C	D	E	F	G
1	tot	name	#1	#2
2	3	dave	1	2		Avg	1.5
3	7	tim	2	5
4	12	rob	5	7
5	6	gary	4	2
6	16	joe	8	8
7
Sheet1

G2:

=AVERAGE(INDEX(C2:D6,MATCH(MIN(A2:A6),A2:A6,0),0))

See also my other post regarding multiple Min values.