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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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

ADVERTISEMENT

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,209

ADVERTISEMENT

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,209
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,095
Members
425,258
Latest member
brentmitchell

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