Formula IF contains a certain word

[James8761]

Hi,

I have a master document that goes form Cell A1:BG22458. i want to create a summary report with just 6 columns. 5 are a simple link, the other requires a formula.

In Sheet1 I have set my summary tab up. Sheet 2 is the master data. In Cells F1:F22458 all cells contain number of words and most including a colour.

I want to pick the colour up. it is either BLUE, RED, GREEN, BLACK, OR WHITE.

So in Sheet 1 cell C1 i want a formula that looks at Cell F1 of Sheet2 that says (If cell F1 contains the word BLUE OR RED OR GREEN OR BLACK OR WHITE, then return that colour, if none of these colours are in Cell F1 of sheet 2, then return "No Colour")

Greatful for any help!
James

 

[jmacleary]

Hi there.

This formula will look for all the words as written
=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"BLUE","RED","GREEN","BLACK","WHITE"},Sheet2!F1)))>0,"Colour","No Colour")

A couple of points. It does a simple match on the letters, so if your data contains, say blackboard or occurred, then it will match the red bit shown. If all the colours are in them middle of each cell, then adding a space before and after each colour name will work, but this will not pick up colours at the very beginning or very end of the cell. I've just realised that you asked for the colour name, give me a bit of time to work on that.
Regards
John

 

[Aladin Akyurek]

Looks like:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH({"BLUE","RED","GREEN","BLACK","WHITE"},Sheet2!F1),{"BLUE","RED","GREEN","BLACK","WHITE"}),"No colour")