Creating Lists based on values in a table.

Hi,

I am trying to create an array formula that would list all the corresponding rows that contain a certain value.

Here is an example workbook to help illustrate what I'm trying to accomplish:

Book1.xlsx

I need to create a list/table that would show all the row labels that correspond to a value. For example, I need to make a list of all cells with a value of 1 under the "Green" column and list the row label(s) that correspond to 1.

Creating the list would result in following return values for "Green": A,C,E,F in separate rows.

I've been using http://www.get-digital-help.com/2009...okup-in-excel/ as a guide but the formulas did not return a corresponding row label. Pivot tables did not help me accomplish this either.

Could anyone suggest a way to start off creating this formula? Is it possible to do without using an array formula?

This is exactly the result that I'm looking for! What I was trying to do, was to get the result of this without using a separate column (H).

I've gotten a result with:

Formula:

Please Login or Register  to view this content.

I'm just going to find a way to do this for "Red", "Orange", etc. now.

Originally Posted by FDibbins

Try this ARRAY formula.

Enter the color in B10, then use this, copied down...
=INDEX($A$2:$A$7,SMALL(IF(OFFSET($A$2,0,MATCH($B$10,$A$1:$G$1,0-1)-1,COUNTA($A$2:$A$8),1)=1,ROW($A$2:$A$7)-ROW($A$2)+1),ROW($A1)))

Thanks for this! I got it to work with this formula!