Returning data in adjacent cells after MIN function

perdactyre

New Member
Joined
Feb 20, 2004
Messages
4
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
hi - welcome to the board!

see if this helps:
WeddingAddresses_update.xls
ABCDE
1NameNum1Num2
2Dave43
3Tim68
4Rob104
5Mike111
6MikeDave
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

Board Regular
Joined
Nov 3, 2003
Messages
73
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

New Member
Joined
Feb 20, 2004
Messages
4
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

New Member
Joined
Feb 20, 2004
Messages
4
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

New Member
Joined
Feb 20, 2004
Messages
4
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
perdactyre said:
...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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi perdactyre:

Let us have a look at ...
Book4
ABCD
1totname#1#2
23dave12
37tim25
413rob57
56gary42
616joe88
7Lowest Scoredavetim
8average1.53.5
Sheet13


Is this what you are looking for?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
perdactyre said:
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
ABCDEFG
1totname#1#2
23dave12Avg1.5
37tim25
412rob57
56gary42
616joe88
7
Sheet1


G2:

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

See also my other post regarding multiple Min values.
 

Forum statistics

Threads
1,186,165
Messages
5,956,323
Members
438,247
Latest member
UZev

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top