Index Match values with multiple matches

Excel.jpg

I've been at this for about 6 hours. I have 3 main columns. First column has the user id and the second column has it's x position and the third column has its y position. To the side of the table I want to put in a user id number, such as 31, and underneath I want it to list the column of x values and y values for that user. That't it and I cant figure it out. I think having multiple 31's is messing me up. I'm very new to excel and any help is greatly appreciated. Maybe someone can point me to the correct google search term so that I can find something relevant.

Thank you

Thanks for your help. I have done a you said and attached an excel file with everything you mentioned.

I propose a formula

Formula:

Please Login or Register  to view this content.

Here's another option:

E7 =INDEX(B$3:B$77,SMALL(IF($A$3:$A$77=$F$4,ROW($A$3:$A$77)-(ROW($A$3)-1)),ROWS($1:1))) Ctrl Shift Enter

Drag the formula through F7 and then down through row 11.

Thank you everyone for your help and suggestions, I will try them. PM63falcondude could you explain how ROW($A$3:$A$77)-(ROW($A$3)-1)),ROWS($1:1) part of your formula works. I'm interested in how the Row subtraction works and what Rows(1:1)
is. Thanks again everyone.

Glad we could help.

The array formula from post #5 is a common way of returning multiple matches.

Basically, the SMALL IF function returns the kth position of the 1st, 2nd, etc TRUE value (where the test is A3=F4, A4=F4, ..., A77=F4).

ROWS($1:1) gives the "k" portion of the SMALL function.
ROWS($1:1) will return 1 (the total # of rows in 1:1), then when the formula is dragged down, it will turn into ROWS($1:2) which will return 2 (the total # of rows in 1:2), etc.