Excel - List data from 4 columns

Question

I have an excel sheet where I want to make a list based on data in 3 columns. See the image for how the basic things looks currently.

I have names in column A and then in column B,C and D is a mixture of colours.

How can I get excel to list the names of those who choose for example Red despite Red possibly being in B,C or D? So it looks a little like:

• Harry Red
• Sara Red
• Sue Red

When it's finished it will have something like 2500 rows and "colour" choice of 27.

The output hopefully would look like this image with the ability to choose which colour it looks for

Answer 1

We can use a "helper column" to gather all the names associated with the colour Red without using array functions. In E2 enter:

=IF(OR(B2="Red",C2="Red",D2="Red"),1+MAX($E$1:E1),"")

and copy down. Then in some other cell enter:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),E:E,0)),"")

and copy down:

The "helper column" identifies the rows of interest. Other colours can be handled in a similar fashion.