Excel 2007 : vlookup returning an array of values

[acallidus]

I have an array of records with one value that repeats:
Key_1 Val_a
Key_2 Val_b
Key_1 Val_c
Key_3 Val_d
Key_2 Val_e
Key_1 Val_f
Key_1 Val_g

On a different sheet I would like to find and output all records that have Key_1.
This can be easily done with macros and I am interested about how to do that using standard excel functions.


Thank you!
A

[FDibbins]

Did you try a pivot table?

[acallidus]

Did you try a pivot table?

unfortunately, that is not possible. the initial data table is automatically generated by internal system.

I have been thinking about any other function, that will return the row numbers or positions in the array. In this case I will be able to use indirect() function. But my research has gave me no results. Is there any function like the one I have described?

[NBVC]

Assuming data is in Sheet1, A2:B10 then to get the values from other sheet:

=IFERROR(INDEX(Sheet2!$B$2:$B$10,SMALL(IF(Sheet1!$A$2:$A$10="Key_1",ROW(Sheet1!$A$2:$A$10)-ROW(Sheet1!$A$2)+1),ROWS($A$1:$A1))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down. Note: You can replace "Key_1" with an input cell reference

[acallidus]

Thanks a lot! That worked.

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save