Index Match Function - NEED HELP PLEASE!

[kylejs7]

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).

 

[Aladin Akyurek]

Try to post a small sample along with the desired results, using an appropriate method: http://www.mrexcel.com/forum/about-board/508133-attachments.html or https://app.box.com/s/soezox25h3w0q5s4rcyl.

 

[kylejs7]

I cannot open the attachment

 

[Aladin Akyurek]

I cannot open the attachment

It also says Download... You can download the file and use as any other add-in for Excel.

 

[Msgjazz]

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?

 

[Aladin Akyurek]

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:

=COUNTIFS($G$2:$G$6,C$2)

C5, control+shift+enter, not just enter, and copy down:

=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))),"")

 

[Msgjazz]

Thank you. Works like a charm

 

[Aladin Akyurek]

Thank you. Works like a charm

You are welcome.