Searching a column for a string in one array, and returning a value that corresponds to that array

Question

I am going to do my best to describe this. I want to search column C for any of the strings contained on a different sheet in column A, and if/when one of those strings is found, i want to return the column B that corresponds to the found string.

For example:

Sheet 1:

Column A	Column B	Column C
project 1	2/15/19	Status is Red, because....
Project 2	4/12/20	RAG- Green, PTG is....

And on another sheet I have common ways to describe their statuses in Column A, and in Column B, a simple letter R, A, G or NS (Not started) if no other common status is found.

Column A	Column B	Column C
Status is Red	R
Rag- Green	G

How can I Search Column C for a value on Column A (on a different sheet) and return column B in Column D on the original sheet? Results should look like

Column A	Column B	Column C	Column D
project 1	2/15/19	Status is Red, because....	R
Project 2	4/12/20	RAG- Green, PTG is....	G

Answer 1

This may be a bit of an extended function and depends on how many rows you have in the second sheet.

You can hard code it with

=IF(ISNUMBER(SEARCH("Red",C2)),VLOOKUP("Status is Red",Sheet2!$A$2:$B$3,2,FALSE),IF(ISNUMBER(SEARCH("Green",C2)),VLOOKUP("RAG - Green",Sheet2!$A$2:$B$3,2,FALSE),"NS"))

Or use the more dynamic:

=IF(ISNUMBER(SEARCH(Sheet2!$A$2,Sheet1!C2)),VLOOKUP(Sheet2!$A$2,Sheet2!A2:B3,2,FALSE),IF(ISNUMBER(SEARCH(Sheet2!$A$3,Sheet1!C2)),VLOOKUP(Sheet2!$A$3,Sheet2!$A$2:$B$3,2,FALSE),"NS"))

Explanation

• SEARCH(find_text,within_text) - returns the index of where the substring begins
• ISNUMBER(value) - returns TRUE if a number, FALSE if not.
• VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) - finds the value within the array and returns the corresponding value in the specified column.
• $X$Y - the dollar signs stop the values from changing when you use ctrl+d or ctrl+r to fill multiple cells

So to break it down:

• IF (search returns an index)
• THEN VLOOKUP (find this value and return the corresponding value from this column)
• ELSE (repeat above as needed)
  
• ELSE ("NS")