Reverse Index Match return names from table data

[Jissenka]

Hi,
Hoping someone can help,
I have a table with names in the left column and columns of the months
the table is populated with data (Ys) (this represents if someone did something in the month)


I want to enter a month in a cell (G1 in example)
An have alist created of the names that correspond to having a "Y" in the column matching the month.

For example if I entered Jan I want to return a list of the names that have a "Y" in the Jan column.

I hope this is clear.
EDIT: to clarify my request, I am hoping for a none VBA solution.
also hoping to use the solution in another worksheet where the number of columns may be very large.

please let me know if more info is needed.
I searched but could not find anything, I may not be seaching the correct terms?)
Capture.JPG
Regards,
Brad

[alansidman]

Here is some Air Code (untested as you did not supply a worksheet to test).

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[Jissenka]

Thank you Alan,
I as hoping for non VBA answer, I didn't specify that though my thought was that posting the formula & functions forum was enough, My fault,
If I can't get a non VBA solution I will work with this.
Again Thank-you for your prompt response.

Brad,

[JeteMc]

I think that it is great to have both a VBA and Formula solution so that you can compare speed, versatility etc.
This proposed solution is based on the following array entered formula* as applied to the screen shot in post #1:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[Jissenka]

Thank you JeteMc,
This worked just as I needed, once I got my head around and modified it.
Brain not working a quick as it should have this morning.

I will be keeping both these solutions tucked away in my tool kit.
and I agree with you on the usfulness of both solutions.

Regards,
Brad

[JeteMc]

You're Welcome and glad that you were able to modify the formula to fit your needs. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.