# Index Match Function - NEED HELP PLEASE!

#### kylejs7

##### New Member
I am trying to take a list of participants - Name, Age, Sport and use a formula to automatically put them into lists (Baseball, Soccer, Golf) sorted by age.

Can someone please show me an example of index match function that I need to use.

I was trying this function but it would not sort from oldest to lowest and it also returns an error for names that dont exist.

=INDEX(\$AP\$1:\$AP\$60, SMALL(IF(\$Y\$3=\$AQ\$1:\$AQ\$60, ROW(\$AQ\$1:\$AQ\$60)-MIN(ROW(\$AQ\$1:\$AQ\$60))+1, ""), ROW(A2)))

Not sure what all of that is (never worked with a function like this).

### 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.
I cannot open the attachment

I cannot open the attachment

I have an INDEX formula which works, but the result is off by one line row

The lookup # in cell \$C\$2 is 0000005631. the result I get is below

G H
In Office Case Number
0000005631 0002736957
0000005631 0005916345
0000005848 0001733195
0000006712 0003105238
0000007579 0004972265

LookUp Results
0005916345 *****This is the second row instead of the first
0001733195 *****This is the third row instead of the second

The result should be
0002736957
0005916345

I'm using:
{=INDEX(\$G\$2:\$H\$6,SMALL(IF(\$G\$2:\$G\$6=\$C\$2,ROW(\$G\$2:\$G\$6)),ROW(1:1)),2)}

Where is my error?

Last edited:
I have an INDEX formula which works, but the result is off by one line row

The lookup # in cell \$C\$2 is 0000005631. the result I get is below

G H
In Office Case Number
0000005631 0002736957
0000005631 0005916345
0000005848 0001733195
0000006712 0003105238
0000007579 0004972265

LookUp Results
0005916345 *****This is the second row instead of the first
0001733195 *****This is the third row instead of the second

The result should be
0002736957
0005916345

I'm using:
{=INDEX(\$G\$2:\$H\$6,SMALL(IF(\$G\$2:\$G\$6=\$C\$2,ROW(\$G\$2:\$G\$6)),ROW(1:1)),2)}

Where is my error?

Your error lies in the source where you picked up this formula with ROW(1:1)...

 Row\Col C​ D​ E​ F​ G​ H​ 1​ In Office Case Number 2​ 0000005631 0000005631 0002736957 3​ 2​ 0000005631 0005916345 4​ Results 0000005848 0001733195 5​ 0002736957 0000006712 0003105238 6​ 0005916345 0000007579 0004972265 7​ 8​

C3, just enter:
Rich (BB code):
``````
=COUNTIFS(\$G\$2:\$G\$6,C\$2)
``````

C5, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=IF(ROWS(C\$5:C5)<=C\$3,INDEX(\$H\$2:\$H\$6,
SMALL(IF(\$G\$2:\$G\$6=C\$2,ROW(\$G\$2:\$G\$6)-ROW(\$G\$2)+1),
ROWS(C\$5:C5))),"")
``````

Thank you. Works like a charm

Replies
1
Views
29
Replies
5
Views
164
Replies
20
Views
541
Replies
8
Views
100
Replies
9
Views
166

1,207,401
Messages
6,078,261
Members
446,324
Latest member
JKamlet

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