Search multiple exact strings in multiple columns

[Sebastian P]

Hello everyone,

I am new to this forum so pardon me if broke any rules through this post.

As the subject of the post states, I am trying to search multiple exact strings in multiple columns. Meaning in the attached file I am searching for certain strings (group names) in a range of cells (multiple columns)

The groups are as per below details:

PUBLIC
GTIS CMUK Windows Approval
GTIS CMUK Windows
UNIX-SUPPORT-FR
MIT Windows
MIT Unix
MIT Windows Approval
MIT Unix Approval
GTIS Hosting Platform Windows

PRIVATE
GTIS Hosting Platform Taurus Approval
GTIS Hosting Platform Taurus
GTIS Hosting Platform Atlas
GTIS Hosting Platform Atlas Approval

The formula I used is:
=IF(OR(ISNUMBER(SEARCH({"GTIS CMUK Windows Approval","GTIS CMUK Windows","UNIX-SUPPORT-FR","MIT Windows","MIT Unix","MIT Windows Approval","MIT Unix Approval","GTIS Hosting Platform Windows"},Table2[@[Group 1]:[Group 6]]))),"PUBLIC",IF(OR(ISNUMBER(SEARCH({"GTIS Hosting Platform Taurus Approval","GTIS Hosting Platform Taurus","GTIS Hosting Platform Atlas","GTIS Hosting Platform Atlas Approval"},Table2[@[Group 1]:[Group 6]]))),"PRIVATE","NONE"))

I have the following points for which I would need support:
1. Is the formula looking in the entire array A2 to A17, by using Table2[@[Group 1]:[Group 6]] in the syntax? I am asking this because:
------>The value returned in cells G4-G8 should be PUBLIC, but the formula does not find a match, though the UNIX-SUPPORT-FR group is in the PUBLIC category

2. How can adapt the formula to search for an exact match. From what I managed to figure out, because the formula does not look for an exact match, the following happens

------>The value in G9-G17 should be PRIVATE, but because the formula does not searches for an exact match, it returns PUBLIC
------>This happens because in one of the Group1 to Group 6 column there is a partial match of the searched text (bolded in below
------>"GTIS Hosting Platform Windows" -->this is PRIVATE
------>"GTIS Hosting Platform Atlas Approval"-->this should be PUBLIC

Thank you in advance,

Column A Column B Column C Column D Column E Column F Column G
Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group Category
GTIS Hosting Platform Atlas Approval GTIS Hosting Platform Atlas ` GTIS CMUK Windows Approval GTIS CMUK Windows GTIS CMUK Windows NONE
GTIS Hosting Platform Atlas Approval GTIS Hosting Platform Atlas GTIS Hosting Platform Atlas GTIS CMUK Windows Approval GTIS CMUK Windows GTIS CMUK Windows PRIVATE
APP-SRE-EI APP-SRE-EI APPOPSFLS-EIKON APP-SRE-EI UNIX-SUPPORT-FR APPOPSFLS-EIKON NONE
APP-SRE-EI APP-SRE-EI APPOPSFLS-EIKON APP-SRE-EI UNIX-SUPPORT-FR APPOPSFLS-EIKON NONE
APP-SRE-EI APP-SRE-EI APPOPSFLS-EIKON APP-SRE-EI UNIX-SUPPORT-FR APPOPSFLS-EIKON NONE
APP-SRE-EI APP-SRE-EI APPOPSFLS-EIKON APP-SRE-EI UNIX-SUPPORT-FR APPOPSFLS-EIKON NONE
APP-SRE-EI APP-SRE-EI APPOPSFLS-EIKON APP-SRE-EI UNIX-SUPPORT-FR APPOPSFLS-EIKON NONE
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Windows Approval MIT Windows MIT Windows PUBLIC
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Unix Approval MIT Unix MIT Unix PUBLIC
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Windows Approval MIT Windows MIT Windows PUBLIC
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Unix Approval MIT Unix MIT Unix PUBLIC
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Windows Approval MIT Windows MIT Windows PUBLIC
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Windows Approval MIT Windows MIT Windows PUBLIC
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Unix Approval MIT Unix MIT Unix PUBLIC
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Windows Approval MIT Windows MIT Windows PUBLIC
GTIS Hosting Platform Taurus Approval GTIS Hosting Platform Taurus GTIS Hosting Platform Taurus MIT Windows Approval MIT Windows MIT Windows PUBLIC

 

[Fluff]

You cannot use spill ranges in a table, hence you get the #spill error.