Quote Originally Posted by NBVC View Post
So, you are correct the MATCH() doesn't work since some cells have more than 255 characters.. but if MATCH() did work, it simply tells you the cell that has the match with A2:O2, If more than one word from Data sheet matched the same cell in A2:O2, then you wouldn't know which was the last word to match within that cell.... so that is not what you want, correct?
The hierarchy i need is from the list of terms from the Data sheet (so, A2:A4).

For example: If the term in 'Data' A3 is found anywhere in, say 'AWorkbooin' A2:O2 then the the term from 'Data' B4 is used to populate row in column C where that term is present in the corresponding row on the AWorkbooin sheet .....unless the term in 'Data' A4 is present anywhere in say 'AWorkbooin' A2:O2

To summerise that:

The hierarchy is the list of terms in 'Data' column A. The position in the cell (on AWorkbooin A:O) where the term appears isn't of interest.

Hope that answers you.

Quote Originally Posted by NBVC View Post
If not, then SEARCH() is better, because it finds the exact position in the cell that each word is found... so we can look for MAX search results... the only thing there is, that SEARCH looks for each of the words in the Data sheet in every cell of the A2:O2 range, yielding an array of the number of cells in Data sheet * the number of cells in A2:O2... so I am having difficulty trying to split it out correctly so that you can line up those results to find last cell in Data sheet to match.

I will have to think it through.. maybe ultimately it might need VBA... not sure at the moment....
Really appreciate your persistence