Hello, I have a search list called categories with each item in the list having a rank:
A2: employee safety
A3: end of life
A4: life
A5: lifespan
A6: patient safety
A7: quality

B2:3
B3:1
B4:4
B5:4
B6:3
B7:2

I am using this formula to return the value found in the string being searched "End of life span",
{=INDEX($A$2:$A$7,MAX(IF(ISERROR(SEARCH($A$2:$A$7, S11)),-1,1)*(ROW($A$2:$A$7)-ROW($A$2)+1)))}

The formula returns "life" but I want it to return "End of life", I tried using min in the formula, but to no avail. I need this to use Vlookup to find the rank of the item found, as the "ranks" will be tallied and scored.
Any help is appreciated.