Extracting text from strings in a single cell from a row of cells
Hi,
Is there an easier way to lookup a text / word in a string within row of cells and where a match is found return a header text - see example shown below.
The table on the left contains the original compliance positions in groups by companies. I would like to populate the table on the right with compliance positions shown in the table on the left, using the header text (red box). Example 1 shows that "Company C" is non-compliant in left table and returns a "Non-Compliant" in the relevant cell for company C. The 2nd example Company's A, C, and Z are compliant in the left table and returns "Compliant" in the relevant cell for company's A, C and Z respectively. The same applies for the Partials.
The rows must also align as shown as the leftmost column will contain a unique reference (not shown in the example).
My current approach was to simply build a small matrix and use IF(COUNTIF($E5,"*Company A*"),"Compliant","") - This works fine, but is a little untidy - The resulting table shown above is really what I would like o achieve.
I also attempted to use nested "IFs", "Find", "Index" and "Match" combinations, based on the some early posts, but just could not get the syntax correct.
Would be grateful for alternative ways to the build the table on the right.
Thank you in advance ...
Pasel
MacBook Pro, Mac OS X (10.7.5)