Search cells to see if it contains text from list and return name of list

[tuxbird]

Hi,

I have several lists e.g.

Colors
Red
Blue
Green
Yellow

Shapes
Round
Square
Rectangular

Where I have named the cells so the first list is called Colors and the second list is called Shapes.

Then I have a table that looks like:

TYPE COST REVENUE
Round 15 45
Square 10 20
Green 8 16

I want to search in the column "TYPE" to see which list the "TYPE" text is part of so the result would be:

TYPE COST REVENUE CATEGORY
Round 15 45 Shape
Square 10 20 Shape
Green 8 16 Color

Can anyone help me with this?

Thanks

 

[bhos123]

=if(ISNUMBER(MATCH(A2,D:D,0)),"Colors",if(ISNUMBER(A2,E:E,0)),"Shapes",""))

Assuming you have colors in Col D and Shapes in Col E

 

[AliGW]

=if(ISNUMBER(MATCH(A2,D:D,0)),"Colors",if(ISNUMBER(A2,E:E,0)),"Shapes",""))

Assuming you have colors in Col D and Shapes in Col E

The OP has named ranges, so this should work:

=if(ISNUMBER(MATCH(A2,Colors,0)),"Colors",if(ISNUMBER(A2,Shapes,0)),"Shapes",""))

 

[tuxbird]

Thanks for your help.

I can get it to work but only for one of the lists

=if(ISNUMBER(MATCH(A2,Colors,0)),"Colors")

When I use more than one I get the error: "Too many arguments were entered for this formula"

Am I doing something wrong?

 

[bhos123]

=if(ISNUMBER(MATCH(A2,Colors,0)),"Colors",if(ISNUMBER(MATCH(A2,Shapes,0)),"Shapes",""))

 

[tuxbird]

Thanks so much - now everything is working perfectly!

 

[bhos123]

you are welcome!

 

[AliGW]

you are welcome!

What he said!