# Returning data in adjacent cells after MIN function

#### perdactyre

##### New Member
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.

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"

##### MrExcel MVP
hi - welcome to the board!

see if this helps:
ABCDE
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
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
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

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.

Thanks again.

#### perdactyre

##### New Member
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

##### MrExcel MVP

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
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?

##### MrExcel MVP
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))

Replies
9
Views
120
Replies
1
Views
536
Replies
1
Views
105
Replies
0
Views
254
Replies
4
Views
156

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

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.

### Which adblocker are you using?

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

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